# Find distance from a point to linestring

I have table (shapetable) with my coastline shape file 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

thanks vic

EDIT: Being relatively a newbie at this, I'm not using PostGIS. I am using sql server 2008. I imported the shape file 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.

Thanks again

-
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 '11 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. – geographika Jun 29 '11 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 '11 at 19:47