# Find distance from a point to linestring

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.

• What software are you using. I'm guessing PostGIS but we need to know for sure before we can answer correctly. Jun 28, 2011 at 21:44
• 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. Jun 29, 2011 at 10:33

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
``````
• 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