The following is an example of a gml:Curve that was loaded into Postgres using FME.

<gml:Curve srsName="urn:ogc:def:crs:EPSG::27700" gml:id="os.vmd.ny.38124.geom"> <gml:segments> <gml:LineStringSegment> <gml:posList>379352.97 580000 379387.02 580022.77 379392.7 580028.98 379412 580031 379419.11 580023.97 379420 580016.95 379446.92 580000</gml:posList> </gml:LineStringSegment> <gml:LineStringSegment> <gml:posList>379446.92 580000 379508 580004.5 379521 580016.5 379529.14 580047.36 379531.91 580057.92 379552.03 580087.79 379559.12 580095.84 379606.5 580115.5 379618 580112.5 379630.79 580097.79 379636.51 580091.2 379667.5 580050 379717.24 580096.52 379729.15 580088.65</gml:posList> </gml:LineStringSegment> </gml:segments> </gml:Curve>

PostgresSQL accepted the geometry but all attempts to do anything with it yielded the error:

ERROR: Unknown geometry type: 9 - CompoundCurve

including ST_IsValid(geom). Therefore, there is no way to even find out the unique id of the offending geometry, as

SELECT id FROM water WHERE ST_IsValid(geom) = 'f' 

returns this error rather than the id.

Is there anyway around this?

Update following comments: It should be noted that all functions return this error, including ST_GeometryType(geom), and ST_AsText(geom). @Vince suggested using a binary search to find the offending rows, which would work, but with millions of rows and hundreds of invalid geometries, would not be fast.

As a related question is there any reason why ST_IsValid returns an error, rather than regarding an error as valid = false.

Fortunately, I have a shapefile of the same area, in which the geometry is encoded as a MultiLinestring, but I would like to avoid having to reload the entire table if possible.

  • 1
    You can use a binary search algorithm -- Use id queries with a WHERE clause of "id >= minval and id <= maxval" to locate the queries that fail. When minval == maxval, you've located the corrupt geometry. – Vince Jul 15 '15 at 12:06
  • @Vince. True. It is a large dataset and there are a number of broken geometries so it would take a while, but if there is no alternative that would work. – John Powell Jul 15 '15 at 12:16
  • 1
    ST_GeometryType() and then just look for 'CompoundCurve' – Paul Ramsey Jul 15 '15 at 12:20
  • @PaulRamsey all functions return the above error. I should have been more clear. I already tried that. I have no idea why Postgres/Postgis accepts a geometry it regards as invalid but that seems to be the case – John Powell Jul 15 '15 at 12:31
  • 1
    It sounds like you have a solution that works for you, but I did have our database team lead check it out anyway. He loaded that feature into 2.1.5 using FME and was able to use SQL commands correctly on the result. He says the binary form identified it as type 09, which is correct too for a compound curve. So, neither of us can really tell what the problem is, or if it was a loading problem. I guess you're all set up now anyway, but we'd be happy to help you (or anyone reading this) explore the issue more if it's necessary (contact us via safe.com/support). Cheers. – Mark Ireland Jul 16 '15 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.