converting geometry from esri st to postgis native using sql

Question

Hi there.

I have been trying to convert between esri's st geometry to postgis geometry by using sql. I'm having trouble when there are more de 2 coordinates, eg when there's m and/or z coordinates.

What I tried is to convert from st to well known formats (wkb, wkt, ewkb, or ewkt), and from these build a postgis geometry using it's available sql constructors. For instance:select st_geomfromewkb(st_asbinary(shape)) from tablename
select geometrytype(geomfromewkb(st_asbinary(shape))) from tablename

For instance, if my tablename has lineZ or lineM, postgis detects it as being of type point...

What I've found is that esri functions return geometry types that are not recognized by postgis, like LINESTRING ZM. Postgis uses "plain" LINESTRING in these cases.

So there is a mismatch in geometry type names that esri uses that prevent the use of geometry constructors in postgis, even though we are in "interoperability land"...

Has anyone found this problem? Any solution or suggestion?

Thanks,
Duarte Carreira

Duarte_Carreira · Answer

hmm. i have to rephrase this. I can create z-m-zm coordinates in postgis 1.4, using the 2d geometry types strings (POINT, LINESTRING, etc.). Postgis parses the coordinates to see there are z or m. I just cannot use the geometry types strings that esri uses "POINT Z", "LINESTRING Z", etc.

Duarte

Duarte_Carreira · Answer

Hello Vince. Thanks for your answer.

I was convinced the ogc sfs 1.2 referred to suffixes without any spaces (eg. POINTZ). I will take this question to the postgis folks. I'll post here if I find a solution. Anyway, I cannot get postgis 1.4 to create a Z,M, or ZM feature using well known text. Only 2.0 (maybe 1.5?) does this.

Regards,
Duarte

VinceAngelo · Answer

If PostGIS doesn't support the well-known text specification (ISO standard), then I'd think
it would be their responsibility to provide a filter mechanism to the portion of the standard
they do support (or simply ignore the extra dimensions).

It looks like ST_GeomFromEWKT comes the closest, but that you'll need to prepend the SRID
as a string and replace the standard "LINESTRING ZM" to their private "LINESTRINGZM"
(or "LINESTRINGMZ", since the online doc doesn't say what it might be).

Writing your own custom filter to strip Z/M text is probably possible, but you'd need to consult
with a PostgreSQL folks on how to write a custom SQL function that accepts TEXT (CLOB)
and returns TEXT.

- V