Subscribe to this thread
Home - General / All posts - Programmatic spatial selection of features: "select by extent" in M9
gjsa100 post(s)
#07-Apr-19 05:24

This topic is about doing a GeomClip or an Overlay-Transform on a very large postgres layer using a much smaller overlay feature in a separate drawing.

Here is the most efficient workflow I have found in QGIS3 and an equivalent approach is also the most efficient in M9, however, I don't know how to programmatically select by extent in M9 without actually using CTRL-left click to select features. "Extent" here could refer to window/map extent or drawing extent.

The objective is to subset the large postgres layer by row selection before doing a transform or the transform operation takes (potentially) hours.

In QGIS3:

1. Use "Extract/clip by extent" on the large layer with the extent of the overlay layer. Note that the large layer is composed of millions of rows, so there is no advantage or penalty ticking the "clip features to extent" option.

2. Clip the Extracted layer with the Overlay layer

In Manifold 9:

1a. Load the overlay drawing into a map and then load the large postgres layer into the same drawing. Wait for the features in the postgres layer within the extent of the map to display.

1b. Select features in the large postgres layer in M9 using the mouse.

2. Ensure that "restrict to selection" is selected before doing an "Overlay Topology, Intersect" on the large postgres layer.

Dimitri


7,413 post(s)
#08-Apr-19 07:43

Questions / ideas...

In 9, what is the time difference between selecting first and doing the overlay, or just doing the overlay without selecting first?

Why not just do it in SQL?

You could also first do a "touching" to get a subset of the big table, only those objects which touch those in the small table. Next, do the overlay between the subset and the small table.

What is a "very large" PostgreSQL layer? (How many objects, etc)

If you must leave your data in PostgreSQL, why not use Manifold to launch a native SQL query within PostgreSQL that does all this server side?

gjsa100 post(s)
#17-Apr-19 02:59

Thanks, I will reply as soon as possible with some comparisons based on your suggestions and a few of my own.. Am talking about a layer with hundreds of thousands or millions of polygons.

Basically, my previous approach in QGIS was to clip by extent, which works instantly to provide a subset of features to then do an intersection/union transform with.

That is the power of WFS, that it will supply features only covering the extent requested, which if grabbed into a (temporary) layer, can then be manipulated without having to search through all the features. Unfortunately, I cant get Manifold to select remote features as quickly using WFS or postgres direct. Overlay touching still takes a lot of time (many minutes at least, on this layer). Selecting features on screen is fast with the mouse, but when trying to copy those features, the slow trawl through the remote dataset begins....

In this respect, Manifold and QGIS must be taking a different approach to copying selected WFS features. Perhaps there's an efficiency to be gained here with Manfiold's approach in future, but either way, there are more than one ways to skin a cat and I will provide some suggestions for the (currently) fastest approach in MF9 soon.

Dimitri


7,413 post(s)
#17-Apr-19 14:13

That is the power of WFS,

Your original post said nothing about WFS. It mentioned Postgres.

Postgres (I prefer to write out the whole thing... PostgreSQL) is a DBMS. WFS is a techology for serving maps on web sites.

If your data is in PostgreSQL, the fast way to get what you want is to connect to the PostgreSQL server and do it fast. The brutally slow and inefficient way is to connect to a web site, where some sort of web server middleware is taking data from a PostgreSQL DBMS and presenting it on the web.

So... to add one more question: why don't you just connect directly to the PostgreSQL DBMS?

You can connect with Manifold to PostgreSQL database and select only those features you want from the PostgreSQL database using a server-side query, and then just work with those in Manifold. That's much faster than using WFS.

gjsa100 post(s)
#18-Apr-19 00:02

My mistake. This is a somewhat specific use case where I have a PostgreSQL/PostGIS database and Geoserver running in a Linux VM on the same Windows machine in which I use Manifold. So I have tried using both WFS (from Geoserver) or PostgreSQL/PostGIS to serve the data into Manifold. The WFS approach is faster than direct DBMS access to grab features within an extent in QGIS - and generally very fast if done in two steps 1. select by extent, then 2. clip extracted features.

Neverthless your suggestion has proved correct: get the DBMS to do the subsetting. Until now I have had difficulty getting the query to work fast on the server side, but this query can do the selection and clipping in one. In the code below, s35 is the large layer I want to select and clip features from within the extent of the small layer s26:

SELECT s26.id,

CASE WHEN st_coveredby(s26.geom, s35.geom)

 THEN s26.geom

 ELSE st_multi(st_intersection(s26.geom, s35.geom))

END AS geom

FROM (internal.s26 s26)

JOIN external.s35 s35 ON

((st_intersects(s26.geom, s35.geomAND (NOT st_touches(s26.geom, s35.geom)))));

In my example, where s35 has 4,200,000 polygons, the query takes 54 seconds to run on the server side. It selects the polygons (2,200 of them) within the extent of s26 and clips them to the boundary of s26.

May be useful for anyone else looking for an optimal way to subset on the server side.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.