4

I have table (shapetable) with my coastline shapefile loaded. I have another table (pointtable) with lat/long points. Wondering how would I go about writing a SQL query to find the distance between the lat/long points and the shapefile. The shapetable has a geography type attribute, from my reading it appears that geometry type linestrings and geography are one and the same.

Appreciate the help

EDIT: Being relatively a newbie at this, I'm not using PostGIS. I am using SQL Server 2008. I imported the shapefile using a shape2sql utility. The table has a bunch of shapefile attributes and also the geography data type. If I understand this correctly, the geography binary data is nothing but linestring of geometry data type (I'd verify this by running a stgeoemtrytype() on the column)

I have another table with a bunch of lat/long. I want to implement a solution where by I want to get distance from the shapefile, using stdistance. One confusing part I found in going through a bunch of documents was that, given a geography data type, how could I find out the linestring coordinates.

Improve this question
2
  • 1
    What software are you using. I'm guessing PostGIS but we need to know for sure before we can answer correctly.
    – Nathan W
    Jun 28, 2011 at 21:44
  • 1
    The Geometry and Geography datatypes are not the same. Geometry values are projected, and are on a flat surface. Geography values assume they are lat/lons on a globe.
    – geographika
    Jun 29, 2011 at 10:33

1 Answer 1

Reset to default
5

First of all you need to make sure that both your coastline features and points are in the GEOGRAPHY datatype and that the coordinates are lat/longs.

Alternatively make sure all your features are in the GEOMETRY datatype and all coordinates are projected (to Mercator, a National Grid etc.).

Then using the STDistance method you should be able to use a query similar to: 

SELECT pointtable.ID,
MIN(pointtable.GEOG.STDistance(shapetable.GEOG)) as MinDistance
FROM pointtable, shapetable
GROUP BY pointtable.ID
  • GEOG field names should be replaced with the field names containing the GEOGRAPHY data.
  • ID field names should be replaced with the appropriate ID field for the table.

If you want to see the actual string represenations of a GEOGRAPHY field use SQL such as: 

SELECT shapetable.ID,
shapetable.GEOG.STAsText()
FROM shapetable
Improve this answer
1
  • Thanks, Wondering if i am in the right diretion with the below query. DECLARE @lat Float; DECLARE @long Float; SELECT @lat = latitude, @long = longitude FROM Points SELECT [dbo].GeoPoint(@lat, @long), MIN([dbo].GeoPoint(@lat, @long).STDistance(US_Shoreline.geom)) as MinDistance FROM [dbo].Points, [dbo].US_Shoreline --function geopoint to get geography type point result SELECT @Result = geography::Point(@Latitude,@Longitude, 4326); return @Result
    – vic
    Jun 29, 2011 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.