[postgis-users] Question to canonical form and SRID

Markus Schaber schabi at logix-tt.com
Mon Apr 10 02:16:53 PDT 2006

Hi, Kai Behncke,

Kai Behncke wrote:
> I know, that I can use "SELECT AsText(the_geom) from xyz",
> but I would like to see the text-from directly.

This will not work on psql or driver level, PostGIS currently has the
machine readable format as default, because the text formats introduced
precision loss / coordinate shifts due to rounding errors in the
floating point parsing.

The exact issues were discussed on postgis-users@/postgis-devel@, you
can search it in the archives.

> And a second question please:
> How do I need to insert the geometry to get back an entry like:
> "SRID=32632;POINT(3455632 6787989)".


INSERT table (the_geom, ...) VALUES (
'SRID=32632;POINT(3455632 6787989)'::geometry, ...)


INSERT table (the_geom, ...) VALUES (setsrid(
'POINT(3455632 6787989)'::geometry,4326), ...)


INSERT table (the_geom, ...) VALUES (GeomFromText(
'POINT(3455632 6787989)',4326), ...)

This insert the geometry with a set SRID.

asText() will not show the SRID, as the OpenGIS standard does not define
the SRID to be a part of the geometry representation.

For OpenGIS, you need to use the srid(the_geom) function to get the
SRID, separately from using asText() or asBinary() for getting the
geometry itsself. Also note that OpenGIS currently does not allow
Measures (4th dimension), and is somehow weak on 3 dimensional geometries.

If you want the above notation using OpenGIS functions only, you can use
'SRID=4326' || srid(the_geom) || ';' || asText(the_geom)

An alternative is the PostGIS function asEWKT, which outputs the
'SRID=4326;POINT(3455632 6787989)' format directly.


Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

More information about the postgis-users mailing list