[GIS] Postgis geometry column with different srid

coordinate systemopenlayers-2postgissrid

I have a table that has geometry column named the_geom. I set SRID value to 29903. And I used Openlayers api to view data on map. I set map srid as 29903 and works well.

But if I need to show my data on google map or another map api that SRID is 4326 or else, should I change my database geometry column SRID.

How can I use multiple SRID for a geometry?

Best Answer

If you don't define SRID for the geometry column at the time of its creation, you can insert geometries with any SRID. I can't think of a reason you would want to do that though. It's reasonable to store all geometries with the same SRID.

I see three options you have:

create new geometry column and fill it with st_transform(geom1, new_srid)
use `st_transform(geom1, new_srid) on-the-fly
use WMS as @iant mentioned

It depends on your use case, how big data is etc.

Related Solutions

[GIS] Override “ERROR: Operation on mixed SRID geometries” and force PostGIS to do the query

You're going to want to replace this construction

st_intersects(proj_shape, st_buffer(cary_proj_point, 5280))

with this one

st_dwithin(proj_shape, cary_proj_point, 5280)

(because otherwise you'll have a terrible scalability problem in the future) and it will won't give you the answer you want unless proj_shape and cary_proj_point both have SRID = 2264. Do they?

[GIS] Ensuring SRID on geometry column in PostGIS

You don't need constraints for PostGIS 2.x, just us typmods. For 2D geometries with SRID=4326, the typmod is geometry(Geometry,4326). Or for M-dimension geometries, it would be geometry(GeometryM,4326) (you get the idea).

Example:

create temp table sometable(wkt geometry(Geometry,4326));

-- works
insert into sometable(wkt) values('SRID=4326;POINT(1 2)')
insert into sometable(wkt) values('SRID=4326;LINESTRING(1 2, 3 4)')

-- does not work
insert into sometable(wkt) values('POINT(1 2)')
-- ERROR:  Geometry SRID (0) does not match column SRID (4326)
insert into sometable(wkt) values('SRID=4326;POINT M(1 2 3)')
-- ERROR:  Geometry has M dimension but column does not

But as others have cautioned, make sure you can use the wkt data in the end-user applications. Also, it isn't technically stored as well-known text—it is stored as well-known binary or WKB.

Best Answer

Related Solutions

[GIS] Override “ERROR: Operation on mixed SRID geometries” and force PostGIS to do the query

[GIS] Ensuring SRID on geometry column in PostGIS

Related Question