[postgis-users] z,m,zm geometries

[Duarte Carreira (gmail)]

unfortunately I could make this work but it's very awkward... there is
something esri does different when encoding wkt geometries that postgis just
finds invalid... namely, using 1.#QNAN000 when a z or m coordinate is
undefined. In the mean time, I found I had been trying this already 
http://postgis.refractions.net/pipermail/postgis-devel/2008-August/003433.html
back in 2008 !! Time flies...

1) convert from esri to text (zm geometries)
select sde.st_astext(shape) from gdbman.srega_adutores limit 1;

LINESTRING ZM ( 36227.79210000 -170369.11470000 0.00000000 1.#QNAN000,
36211.96660000 -170348.55650000 0.00000000 1.#QNAN000, 36201.84180000
-170334.21600000 0.00000000 1.#QNAN000, 36192.28410000 -170319.49250000
0.00000000 1.#QNAN000, 36183.30730000 -170304.40710000 0.00000000
1.#QNAN000, 36174.92650000 -170288.98260000 0.00000000 1.#QNAN000,
36167.15260000 -170273.24310000 0.00000000 1.#QNAN000)

2) I can remove ZM from this string with regexp_replace:
select regexp_replace(cast(sde.st_astext(shape) as text), ' Z| M| ZM','')
from gdbman.srega_adutores limit 1;

LINESTRING ( 36227.79210000 -170369.11470000 0.00000000 1.#QNAN000,...

3) When converting this string to pg_geometry pg says it's invalid:
select geomfromtext(regexp_replace(cast(sde.st_astext(shape) as text), ' Z|
M| ZM','')) from gdbman.srega_adutores limit 1;

ERROR:  parse error - invalid geometry

HINT:  "...210000 -170369.11470000 0.00000000 1." <-- parse error at
position 58 within geometry

It seems #QNAN000 is invalid (this is a undefined value in esri's world). I
can replace it with a ad-hoc value to signal undefined coordinates (eg
-99999), with a new regexp_replace:

3) remove #qnan000
select regexp_replace(regexp_replace(cast(sde.st_astext(shape) as text), '
Z| M| ZM',''),'1.#QNAN000','-99999','g') from gdbman.srega_adutores limit 1;

LINESTRING ( 36227.79210000 -170369.11470000 0.00000000 -99999,...

This finally works well with postgis:

4) create a postgis geometry
select geomfromtext(regexp_replace(regexp_replace(cast(sde.st_astext(shape)
as text), ' Z| M| ZM',''),'1.#QNAN000','-99999','g')) from
gdbman.srega_adutores limit 1;

whew...

Is it worth it?

I ended up using esri's tools to copy from esri_st to pg_geometry by
selecting the appropriate sde keywords...

--
View this message in context: http://postgis.17.n6.nabble.com/z-m-zm-geometries-tp4981255p4997633.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120522/c5a6578a/attachment.html>