[postgis-users] z,m,zm geometries
Hi there. I'm trying to convert from esri's st_ geometry type to postgis' one. I can't convert to wkt using esri's functions and build a geometry in postgis using geomfromtext. Problems start when there are z, m, or zm coordinates. Mind that I'm on postgis 1.4 since that's the latest supported by esri. Now, when I convert from esri using their function I get a text like: LINESTRING Z ... When I pass it to geomfromtext, or even st_geomfromewkt, postgis does not like this... I posted on esri's forum and got a slap in the hand saying postgis is not following the ogc standard... I've done some reading and all I could find are references to POINTZ, POLYGONZ, etc. (suffixes withouth spaces) in v.1.2.0 of the standard. But this does not seem to be the spec for encoding wkt, just the geometry type to be put into geometry_columns. Either way, I could not get postgis 1.4 to accept pointz or any other geometryz type. I found references to postgis 2.0 doing this... but that is beyong my grasp for now. So my 2 questions are: - is there anyway i can get this to work in postgis 1.4 (main question)? - and as long as we're in the subject, how is this related to the ogc sfs spec? are "they" right? Thanks for your time! Regards, Duarte ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] z,m,zm geometries
On Tue, May 15, 2012 at 3:02 PM, Duarte Carreira wrote: > Hi there. > > I'm trying to convert from esri's st_ geometry type to postgis' one. > > I can't convert to wkt using esri's functions and build a geometry in > postgis using geomfromtext. > > Problems start when there are z, m, or zm coordinates. Mind that I'm on > postgis 1.4 since that's the latest supported by esri. > > Now, when I convert from esri using their function I get a text like: > > LINESTRING Z ... > > When I pass it to geomfromtext, or even st_geomfromewkt, postgis does not > like this... > > I posted on esri's forum and got a slap in the hand saying postgis is not > following the ogc standard... > > I've done some reading and all I could find are references to POINTZ, > POLYGONZ, etc. (suffixes withouth spaces) in v.1.2.0 of the standard. But > this does not seem to be the spec for encoding wkt, just the geometry type > to be put into geometry_columns. Either way, I could not get postgis 1.4 to > accept pointz or any other geometryz type. I found references to postgis 2.0 > doing this... but that is beyong my grasp for now. > > So my 2 questions are: > > - is there anyway i can get this to work in postgis 1.4 (main question)? Are you wedded to the Z? Do you have a way to strip the Z from the ESRI geometry before outputting the text? Perhaps you could try WKB instead? (Still might have problems with type numbers, but maybe worth a try?) > - and as long as we're in the subject, how is this related to the ogc sfs > spec? are "they" right? The 1.0 and 1.1 specs made no mention of higher dimensions (and hence provided no guidance on what form the WKT should take). Not sure if 1.2 did, but I don't think so. ISO SQL/MM does talk about them, and that's the pattern ESRI is following. PostGIS 2.0 consumes that kind of text, but that's no solace to you. P. > Thanks for your time! > > Regards, > Duarte > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] z,m,zm geometries
On Tue, May 15, 2012 at 3:02 PM, Duarte Carreira wrote: > - is there anyway i can get this to work in postgis 1.4 (main question)? Oh, you're getting text and PostgreSQL has some quite powerful regex functions. You can probably strip the Z from the text before passing it along to PostGIS. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] z,m,zm geometries
Hi there Paul, thanks for looking at this. I'm going to try pg's regexp,replace() and see if i can come up with an expression that will take out " Z", " M", and " ZM" in one pass. Today I'm out of the office, but will try tomorrow and report back. Maybe regexp.replace(st_astext(geom), '.*Z|.*M|.*ZM' , '') ) will do it. best, Duarte -- View this message in context: http://postgis.17.n6.nabble.com/z-m-zm-geometries-tp4981255p4991926.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] z,m,zm geometries
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.7921 -170369.1147 0. 1.#QNAN000, 36211.9666 -170348.5565 0. 1.#QNAN000, 36201.8418 -170334.2160 0. 1.#QNAN000, 36192.2841 -170319.4925 0. 1.#QNAN000, 36183.3073 -170304.4071 0. 1.#QNAN000, 36174.9265 -170288.9826 0. 1.#QNAN000, 36167.1526 -170273.2431 0. 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.7921 -170369.1147 0. 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: "...21 -170369.1147 0. 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 -9), 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','-9','g') from gdbman.srega_adutores limit 1; LINESTRING ( 36227.7921 -170369.1147 0. -9,... 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','-9','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.___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
