New answers tagged

2 votes

Check if polygon falls inside other polygon with 2 layers

You can look for polygons partially contained by multiple country polygons, as in Zoltan's answer, or you can look for polygons that are fully contained by no country polygon. SELECT * FROM ...
JGH's user avatar
  • 40.8k
1 vote
Accepted

Check if polygon falls inside other polygon with 2 layers

To find polygons overlapped by more polygons from another layer you can use the following query. SELECT a.identifier, a.name, count(*) FROM layer_polygon a INNER JOIN layer_ countries c ON ST_Overlaps(...
Zoltan's user avatar
  • 7,235
0 votes
Accepted

PostGIS: postgis_raster out-db storage errors

Moving my raster files to the C:\ root folder solved my problem. This as to do with the network service account the postgresql service is running on, and it has nothing to do with your user account. ...
bonzinor's user avatar
  • 157
0 votes

QGIS starts a query "ST_SummaryStatsAgg()" on PostGIS3.0 for a raster and needs more than 10min to load

The bug has now been fixed and ST_SummaryStatsAgg should use fast overviews instead with QGIS 3.34.1 and QGIS 3.28.13.
Joonala's user avatar
  • 351
3 votes

How to intersect with ST_Buffer in PostGIS

If you don't specify column names in the CTE, they will have the same names as in the inner select statement, so here the column would be named st_buffer instead of geom. You can set the name at the ...
JGH's user avatar
  • 40.8k
0 votes

How to export PostGIS points as blocks in DXF with ogr2ogr

You should firstly provide a reference DXF file(e.g. my.dxf) in the folder from where you want to execute the ogr2ogr command. It should contain the DXF block you want to use in your exported DXF. For ...
Mihai-Mircea Moise's user avatar
1 vote

Use ST_Distance() to filter a maximum of 1000m

You had better to use CTE (Common Table Expression) to make your query readable. If you have spatial index on geom column then the ST_DWITHIN function is more effective. I suppose you forget to put ...
Zoltan's user avatar
  • 7,235
1 vote

ST_Distance from one location to several locations on the same table

Use a single SQL expression (not nested) referencing the table twice with different aliases: SELECT loc.name AS Einrichtungsname, ST_Distance(loc.geom, pt.geom) AS Distanz FROM gebaeude_salzburg loc, ...
Mike T's user avatar
  • 41.8k
3 votes

Find all geometries in one table that do not exist in another

The plain = implementation for the GEOMETRY/GEOGRAPHY types directly compare their encoded byte sequences, which naturally is much faster than determining geometric likeliness, especially for higher ...
geozelot's user avatar
  • 29.3k
0 votes

ST_Distance from one location to several locations on the same table

It's not beautiful and might not be the fastest approach, but this should be what you want: SELECT gebaeude_salzburg.name AS Einrichtungsname, ST_Distance( geom, (SELECT geom FROM ...
bugmenot123's user avatar
  • 10.7k
0 votes

Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

I am not sure about the PostGIS part. But looks like this is happening because of file size. It may take longer time to load and then draw shapes in canvas. Last time when I wanted to test large ...
Hemixa Patel's user avatar
0 votes

Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

I had the same issue. I uninstalled my version of QGIS of 3.32.3 and downloaded the latest at 3.34.0. Issue fixed.
Bob the Builder's user avatar
0 votes
Accepted

Why the geometries in 32632 projection aren't perfect?

I was able to solve the issue with this query: SELECT ST_AsMVT(tile) FROM ( SELECT __FEATURES__, ST_Transform(ST_AsMVTGeom( ...
Kambei's user avatar
  • 160
0 votes

Why the geometries in 32632 projection aren't perfect?

EPSG CODE 32632 Guess this is probably due to the applicability of 32632.
xyz绿洲's user avatar
0 votes

python - psycopg2.errors.RaiseException find_srid() - could not find the corresponding SRID

I had this same problem, which was from a bug in geopandas where it only looks at the geometry_columns and not the geography_columns table for the SRID. See: https://github.com/geopandas/geopandas/...
schimmy's user avatar
  • 101
2 votes
Accepted

How to call newly made variable

There are many possible, more or less elegant and performant solutions in SQL - this one attempts to stay within basic JOIN set theory. Theoretically you would create a FULL OUTER JOIN result set and ...
geozelot's user avatar
  • 29.3k
1 vote
Accepted

Determine area enclosed by multiple segments

Solution for PostgreSQL/PostGIS. Input geodata table named "linetoarea" shown in Figure 1 below. Figure 1 Run Geo-SQL script: WITH noded AS (SELECT ST_Node(ST_Union(geom)) geom FROM ...
Cyril Mikhalchenko's user avatar
0 votes

Shortest line for multiple points using PostGIS

R.K.'s answer seems not useful since the OP doesn't have a graph but only a set of points without "routes", so a graph can always be done. The TSP and the Local search (optimization) are ...
MGG's user avatar
  • 1
2 votes

Is there a common method for measuring "curviness" of a line?

I'm not sure if this will be of great use to you, it might be too specific, but I have a function that tests for what I call cutbacks in lines. This is where a line bends more than a certain amount ...
jbalk's user avatar
  • 7,475
6 votes

PostGIS fields of type interval not part of QGIS' field list

The supported PostgreSQL data types are documented in https://docs.qgis.org/3.28/en/docs/user_manual/managing_data_source/supported_data.html#support-of-postgresql-data-types Data types supported by ...
user30184's user avatar
  • 63k
5 votes

Trying to get accurate results using ST_DWithin with srid -1

tl;dr: Given your setup, the most optimized, accurate query would be SELECT * FROM location ORDER BY ST_SetSRID(location.point, 4326)::GEOGRAPHY <-> ST_SetSRID(input_point, 4326)::...
geozelot's user avatar
  • 29.3k
6 votes
Accepted

Get convex hull of polygons if they are within a certain distance of each other otherwise leave their geometries alone

Use ST_ClusterWithinWin to cluster the points by distance, and then compute the convex hull (and the buffer if desired) of the clusters. WITH data(id, geom) AS (VALUES (1, 'POINT (10 10)'::geometry)...
dr_jts's user avatar
  • 4,713
0 votes
Accepted

Import GeoPackage(GPKG) to PostGIS with styles

I made some testing by comparing what was stored into layer_styles by ogr2ogr and by saving the styles into PostGIS with QGIS. It appeared that ogr2ogr left three important fields empty: ...
user30184's user avatar
  • 63k
2 votes
Accepted

PostGIS ST_Area() accuracy for huge polygons

Yes, this is the correct answer ... in geography. Let's not forget that geography uses great circles instead of straight lines to connect two points. [-180,-15] to [-180,15] ==> the line follows ...
JGH's user avatar
  • 40.8k
1 vote

Intersecting Node A and Node B columns for lines table

Do you just want to get start and end node information for your LineStrings? I.e. something like SELECT oc.*, sn."ID" AS start_node, en."ID" AS end_node FROM pia.or_chamber ...
geozelot's user avatar
  • 29.3k
6 votes

Is there a common method for measuring "curviness" of a line?

One measure is Sinuosity which is the line length divided by the shortest distance from the line start and end points: select id, st_length(geom)/st_distance(st_startpoint(geom), st_endpoint(...
BERA's user avatar
  • 68.7k
4 votes

Intersecting Node A and Node B columns for lines table

Judging from the table presented as "OUTPUT from QGIS", the result should be unique on column "ID" which, when compared to the "OUTPUT" above, is actually column b."...
Paul van der Kroft's user avatar
3 votes

Set coordinates as default value

Make your database PostGIS ready (e.g. CREATE EXTENSION PostGIS;) Create a table with geometry/geography column and add a default value to the column? CREATE TABLE test1 ( id int primary key, pos ...
Zoltan's user avatar
  • 7,235
4 votes

How to Append a POINT to a MULTIPOINT column

To wrap this up: UPDATE <table> SET geom = ST_Multi(ST_Union(geom, 'SRID=4326;POINT(<lon|x>, <lat|y>)')) WHERE id = <id> ; All covered.
geozelot's user avatar
  • 29.3k
0 votes

How to Append a POINT to a MULTIPOINT column

The query I am using is correct. The issue is that the coordinate I was trying to add was exactly identical to the only other coordiante inside the multipoint column. This throws the error.
bcsta's user avatar
  • 161
2 votes

PostgreSQL/PostGIS database in OneDrive?

I do use a PostgreSQL/PostGIS database in an OneDrive-Cloud and access it with QGIS. I created a folder pg_data within the local OneDrive and specified it as the data storage destination along the ...
jpinilla's user avatar
  • 3,180

Top 50 recent answers are included