开发者

PostGIS SQL query and invalid geometries

I have a postgis database imported with osm2pgsql. Obviously there are lots of invalid geometries which leads to errors with some spatial operations.

geometry.buffer(x) seems t开发者_如何学Pythono solve this problem, but this operation takes a lot of time. So, I wanted to apply it only to geometries that are not valid:

select * from
    (
        select *
        from polygons
        WHERE NOT IsValid(polygons.geom)
    ) as tbl
where ST_Intersects(
    ST_Buffer(tbl.geom, 0.001),
    GeomFromText('POLYGON ((XY))', 4326)
);

But this query seems to apply the buffer operation to all entries in the table. How would you limit this operation to the invalid geometries only?

Thank you in advance!


PostgreSQL optimizer expands the inline views prior to optimizing, so it is not guaranteed that the predicates will not be pushed in or out the views.

The CTE, on the other hand, are always materialized, so this query:

WITH    tbl AS
        (
        SELECT  *
        FROM    polygons
        WHERE   NOT IsValid(polygons.geom)
        )
SELECT  *
FROM    tbl
WHERE   ST_Intersects
                (
                ST_Buffer(tbl.geom, 0.001),
                GeomFromText('POLYGON ((XY))', 4326)
                );

will only apply ST_buffer to the invalid geometries.

However, this does not seem to be a right solution to me. Could please provide a sample of "invalid geometry" produced by the import?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜