3

I have been given the task of:

  • Loading a list of properties with lat/long coordinates into a SQL Server 2008 database.
  • Loading points and areas (polygons) of interest from a variety of shapefiles from various local, state, and national sources.
  • Allowing the user to query the areas of interest within certain distances of a given property.
  • Allow another programmer to extract both the property and points/areas of interest to KML files for visualization in Google Earth.

My problem is that each source of shapefiles uses their own projection and SQL Server cannot perform comparisons on geographical data using different projections, nor can it convert from one projection to another.

I have determined that, using the pyproj library, I am able to convert a point from a given projection to lat / long. I tested this by converting a point location known to be in Pinellas Park, Florida and then displaying the corresponding lat / long in Google maps — and the displayed point is in Pinellas Park (although Google's estimate of the building number is off a little; 5551 XYZ Ave instead of 5541).

My question (at last): is it a safe and reasonable strategy to convert all the points in the incoming shapefiles to lat / long and store them that way, using the SQL Server standard SRID of 4326? Is there any danger of loss of precision or shifting in the conversion? If the converted lat / long polygons are displayed in Google Earth will they be identical to a display produced from the original shape files?

Improve this question

1 Answer 1

Reset to default
2

It is usually a good idea to have all layers in the same projection. It is common to store data in lat/long and give away and perform calculations on re-projected data. But in case when you are not able to perform on-fly (or in query) re-projection (and it seems that you are unable to do it) and have to calculate distances - unprojected 4326 is not suitable for the task. To measure distances you need projection with units in metres, not degrees. If your client software will be able to reproject data and measure distances on its own then it is possible to store data in 4326.

You may loss some precision because of the transformation, but usually it is far too insignificant.

Improve this answer
2
  • It appears to me that SQL Server does allow geographical comparisons in meters even for data stored with a projection of 4326 (of course, I can't do the comparisons manually). I was able to run these comparisons between properties, which I have stored in lat / long coordinates.
    – Larry Lustig
    Apr 27, 2012 at 19:55
  • If it is so - use 4326. I would ran some tests using same 2 layers in 4326 and in other projection in metres just to be sure.
    – SS_Rebelious
    Apr 28, 2012 at 11:25

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.