Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
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 '11 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 '11 at 10:33
add comment

1 Answer 1

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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