Solved

Select records based on encoded polyline value

Posted on 2011-02-20
7
1,282 Views
Last Modified: 2012-05-11
Dear Experts

I have a sql 2008 database that holds the geographical points of properties.  See example of records below:

ref                 Lat                long                point                                  pointlatlon
8192      55.4241      2.78549      <Binary data>      POINT (2.78549 55.4241)
8194      55.6096      2.77599      <Binary data>      POINT (2.77599 55.6096)
8201      55.5923      2.7258      <Binary data>      POINT (2.7258 55.5923)
8206      55.4217      2.78940      <Binary data>      POINT (2.7894 55.4217)
8227      55.5463      2.84920      <Binary data>      POINT (2.8492 55.5463)
8241      55.4374      2.7803      <Binary data>      POINT (2.7803 55.4374)


If a receive an encoded polyline value (for example: polyline=svzrIr%7BbQ%7Cnl@i%7BfAehk@wjuArH%7EeBko@%60%60zC) from a string can someone suggest how I can construct the necessery in SQL to be able to return the records from the database that fall within the polyline?

Thanks

0
Comment
Question by:grantballantyne
  • 4
  • 3
7 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34938137
How is your database constructed. What tables hold the data you need? What data do you need? Can you give some more information please?
0
 

Author Comment

by:grantballantyne
ID: 34938266
Elvin,

Thanks

The example of the data above is from a sql view called 'current_residential_radius_geog'.  Please see the syntax below that was used to create the table.  

This view holds all the data i need for the time being.

If I can return the 'pRef' value from this view where the 'point' value falls within the encoded polyline that would enable me to accomplish what I need.

Thanks for taking the time to answer and if you need any more info then please ask - I have been trying to work this out for weeks without much success to date - however I know its possible somehow!!

 







Create view  current_residential_radius_geog as
SELECT     pRef, pLat, pLong, geography::STPointFromText('POINT(' + CAST(pLong AS VARCHAR(20)) + ' ' + CAST(pLat AS VARCHAR(20)) + ')', 4326) AS point, point.ToString() AS pointlatlon
FROM         dbo.current_residential_radius

Open in new window

0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34938315
Well the first thing that comes to mind is how to decrypt that encoded string ? You will need to get data from that string to use in your query but how to decode it first? I'm not sure how to accomplish this. How what this string encoded?  polyline=svzrIr%7BbQ%7Cnl@i%7BfAehk@wjuArH%7EeBko@%60%60zC

Where does this value come from and how is it encoded? Can it be dissected and decoded?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:grantballantyne
ID: 34938362
Elvin,

The string was encoded via javascript that I have used in my page.  Please see the page (and javascript) at http://www.bspc.co.uk/forsalemap2.asp.

Like you, I am not entirely sure how to decode the string, however if you have a look at the following google api page:  http://code.google.com/apis/maps/documentation/utilities/polylineutility.html  you will notice that you are able to decode the polyline.

Thanks

0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34938498
Yes I see that. Here is the decoding page

http://code.google.com/apis/maps/documentation/geocoding/#ReverseGeocoding
but I read that page and still can't work out how to reverse the code??? It's a little more tricky than I thought. Perhpas you could request that a moderator move this question into the "puzzles and games" section. There are lots of code writers in that section that can read stuff like this. Once you know how to decode it or you have found a function to do this, then we can show you how to extract the information from the decoded string and search the database for results between any two values.

Sorry i can't be of more help.
0
 

Author Comment

by:grantballantyne
ID: 34938592
OK thanks Elvin

Supposing I had a decoded polyline value of (('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))

How could I return the records from the table using this?

Thanks
0
 
LVL 20

Accepted Solution

by:
Mark Brady earned 500 total points
ID: 34938641
I would imagine the easiest way to extract the data you need would be to capture that value as a string, then explode it at the comas ',' to get each point. The first 2 should be your 'Lat' and 'long' values and the last two would be the line orientation. The first of which would be the start point and the 2nd the end point.

Once you know the start and end points and say for eg they are now called $start and $end your query to return all lines that lie between those points would be:


$sql = "SELECT * FROM `your_table` WHERE `Lat` BETWEEN $start AND $end AND `long` BETWEEN $start AND $end ODER BY Lat ASC";

Something along those lines should do it but I'm not sure exactly.

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now