New answers tagged

1 vote

Showing Origins on ARRAY multiple routes - pgRouting

Let's say you want to compute a route from A to B then your start and destination vertex will be in the output as start_vid and end_vid. For the sake of simplicity this query is using some arbitrary ...
user avatar
3 votes

Deleting small holes in polygons with PostGIS

Based on dr_jts response, I propose the following version of the design and name of the new spatial SQL function: Create a spatial custom SQL function: CREATE OR REPLACE FUNCTION ...
user avatar
7 votes

Deleting small holes in polygons with PostGIS

Just to add a little less convoluted solution in SQL, shifting construction logic to ST_BuildArea - the LATERAL join predicate is an elegant method to expand and iterate over sets of rows returned ...
user avatar
  • 23.3k
13 votes
Accepted

Deleting small holes in polygons with PostGIS

This is one of those processes which are simple in an iterative language, but trickier in set-oriented SQL. But the ST_DumpX set-returning functions in PostGIS and the array handling in Postgres make ...
user avatar
  • 3,621
3 votes

Deleting small holes in polygons with PostGIS

Based on your example, just extract the interior ring of your geom, calculate it's area and use it as condition in where clause as: SELECT gid, ST_Collect(ST_MakePolygon(geom)) As geom FROM ( ...
user avatar
  • 423
0 votes

Getting base Line of PostGIS skeleton

A simple approach to "trim the branches" could be by longitude but is not a complete solution. with lines as ( SELECT st_subdivide( st_straightskeleton( ST_GeomFromText('POLYGON (( 190 190, ...
user avatar
  • 423
0 votes

Improve performance of startpoint query (ST_GEOMETRY)

This solution gets the startpoints and the endpoints in a view. Whereas the original question only mentioned startpoints, not endpoints. Create a custom function: create or replace function ...
user avatar
  • 716
4 votes

Virtual layer that shows the union of multiple layers with same schema

Another possible workflow. Step 1. Apply the "Merge vector layers" tool Step 2. Create a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer... with the ...
user avatar
  • 23k
8 votes
Accepted

Virtual layer that shows the union of multiple layers with same schema

You can use python to print out the sql needed in the console, then copy paste it to the virtual layer: root = QgsProject.instance().layerTreeRoot() layerlist = [] #List the layers in surveyor group ...
user avatar
  • 51.4k
0 votes

Counting stacked polygons in PostGIS

Let's assume there is a polygon layer called 'poly_test', see image below. Extending the idea from @geozelot it is possible to count the polygons via grouping by their geometry. With the following ...
user avatar
  • 23k
0 votes

Loading SQL X/Y Table into QGIS

So after a lof of searching I found a VERY simple solution to this issue. Step 1 - add the SQL data as a table to your project Step 2 - add a virtual layer and "import" your SQL table and ...
user avatar
0 votes

Select SDE.ST_GEOMETRY as JSON text (using SQL)

I suppose one option would be to populate a text column with JSON using an ArcGIS attribute rule. And then simply select that column using SQL. The problem with that option is: Not all of our users ...
user avatar
  • 716

Top 50 recent answers are included