grantballantyne
asked on
Select records based on encoded polyline value
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%7Ee Bko@%60%60 zC) 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
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@
Thanks
How is your database constructed. What tables hold the data you need? What data do you need? Can you give some more information please?
ASKER
Elvin,
Thanks
The example of the data above is from a sql view called 'current_residential_radiu s_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!!
Thanks
The example of the data above is from a sql view called 'current_residential_radiu
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
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%7Ee Bko@%60%60 zC
Where does this value come from and how is it encoded? Can it be dissected and decoded?
Where does this value come from and how is it encoded? Can it be dissected and decoded?
ASKER
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
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
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.