10

I'm using PostgreSQL with PostGIS. All my data has already decimal lat/long attached to it (i.e. -87.34554 33.12321) but to use PostGIS I need to convert it to a certain type of SRID. The majority of my queries are looking for data inside a certain radius.

What SRID should I use? I created already a geometry column with SRID 4269. In this example: link text the author is converting SRID 4269 to SRID 32661. I'm very confused about how and when to use these SRIDs. Any lite on the subject would be truly appreciated.

avatar
  • 12,087
  • 17
  • 66
  • 82

2 Answers2

8

As long as you never intend to reproject/transform the data to another coordinate system, it doesn't technically matter what srid you use. However assuming you don't want to throw away that important metadata, and you do want to transform it, you will want to ensure your assigned srid matches the data, so postgis knows what to do when the time comes.

So why would you want to reproject from epsg:4269? The answer is because certain types of queries (such as distance) make no sense in this 'unprojected' world. Your units are in decimal degrees, and a straight measurement of x decimal degrees is a different real distance depending where in the planet you are.

In your example above, someone is using epsg:32661 as they believe it will give them better accuracy for the are they're working in. If your data is in a specific area of the globe, you can select a projection that's accurate for that area. If it spans the entire globe, you have to choose a projection that does 'ok' for your needs.

Now fortunately PostGIS has a few ways of making all this easier. For approx distances you can just use the st_distance_sphere function which, as you might guess, assumes the earth is a sphere. Or the more accurate st_distance_spheroid. Using these, you don't need to reproject and you will probably be fine for your distance queries except in edge cases. Newer versions of PostGIS also let you use geography columns

tl;dr - use st_distance_spheroid for your distance queries, store your data in geography columns, or transform it to a local projection (when storing, or on the fly, depending on your needs).

jlivni
  • 4,759
  • 1
  • 19
  • 30
  • I'm down voting this because of this: "The answer is because certain types of queries (such as distance) make no sense in this 'unprojected' world. Your units are in decimal degrees, and a straight measurement of x decimal degrees is a different real distance depending where in the planet you are." In an "unprojected" word there is a notion of a distance, here is for sphere: (http://en.wikipedia.org/wiki/Great-circle_distance) it just bit more difficult to calculate. As far as distance calculation in lat/lon coordinates this is not a problem in postgis http://postgis.net/docs/ST_Distance.html – Dimon Buzz May 24 '15 at 00:58
  • Fair point - I should have noted distance as in a simple difference of lof the coordinates. But your comment is also misleading: The distance calculation you point to is a form of projecting the lat/lng (onto a sphere in this case) so you can get a "reasonable" distance answer. But it's still projecting. And while PostGIS can do this under the hood for you with geographic coords, st_distance will produce confusing (e.g. simple difference of coords) results if you are using epsg:4326 with geometry columns for example. – jlivni Jun 18 '15 at 06:00
  • It's not misleading, and I'm not stating that "lat/lon are projected into sphere" I'm saying that on a sphere there is a notion of a distance, and you don't have to project a sphere to feel it. lat/lon is simply a coordinate system to describe points on the sphere. They have nothing to do with projections, which are a way to transform the N dimensional space (sphere in our case) into a another M dimensional space (usually a Cartesian 2 dim plane), where notion of distance is more intuitive and clear. Projection is the result of transformation from one coordinate system to another. – Dimon Buzz Jun 19 '15 at 19:06
  • Technically lat/lon is not a coordinate system to describe points on a sphere. For example, you will get different lat/lngs from a GPS with epsg:4326 vs epsg:4269. Yes it's relatively easy to do spherical math, but lat/lngs do not live in a vacuum or a sphere. Sometimes they are used on a spheroid, sometimes on a sphere, and sometimes on non-earth planets. They are merely units, and when considering how they relate to a spot on earth projections must come into play. None of this is relevant to the OP however (tho I do regret my "unprojected" comment now). – jlivni Jun 20 '15 at 03:38
  • Thanks for the point. Correct, when one defines a coordinate system as far as GIS are concerned, he talks about datum, which is defined by the shape of the ellipsoid, shift from the prime meridian and set of coordinates to parametrize the points on the ellipsoid's surface (lat, lon or x,y,z, etc). For that matter WSG84 ellipsoid is 0.21mm longer then one used in NAD83. But again you don't have to project to perform distance calculations. You can do that in "unprojected" i.e. (lat, lon) coordinates just fine. – Dimon Buzz Jun 20 '15 at 14:55
1

Take a look at this question: How do you know what SRID to use for a shp file?

The SRID is just a way of storing the WKT inside the database (you may have noticed that, altough you store lat/long points, the preferred storing is a long string with number and capital letters).

The SRID or EPSG can be different for the country/state/... altough there are some very common ones especially the 2 mentioned by you. If you need specific info what area uses what SRID, there is a database for handling that.

Inside your database, you have a table spatial_ref_sys that has the information on what SRID PostGIS knows about.

Community
  • 1
  • 1
DrColossos
  • 12,656
  • 3
  • 46
  • 67
  • As I like to say " better real confusion than false clarity". Anyway my confusion came from the fact that the example in this page: http://unserializableone.blogspot.com/2007/02/using-postgis-to-find-points-of.html converted the 4269 to 32661 every time he made query. Does this have to do with the fact that in the end he might have some important data that's 32661 that's why he always converts it to that system? Otherwise the 4269 would be fine? – avatar Jan 17 '11 at 14:41
  • @itgorilla It depends on the way you output it. most of the time, a JS library will handle the display (like Google Maps, OSM,...) and this library needs to place the lines, points,... somewhere and if the underlying corrdinates are needed in 4269, than he needs to convert it respectivly. I'm no expert though, so maybe someone has a better explanation for it. – DrColossos Jan 17 '11 at 14:47
  • As you can see I'm trying to figure this out myself :). Anyway I think the more I read and ask I'm beginning to get a more clear picture how this works. I appreciate your help. – avatar Jan 17 '11 at 14:54