New answers tagged sql
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 ...
- 817
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 ...
- 3,912
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 ...
- 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 ...
- 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 (
...
- 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, ...
- 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 ...
- 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 ...
- 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
...
- 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 ...
- 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 ...
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 ...
- 716
Top 50 recent answers are included