New answers tagged postgis
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 ...
- 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(...
- 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. ...
- 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.
- 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 ...
- 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 ...
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 ...
- 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, ...
- 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 ...
- 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 ...
- 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 ...
- 51
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.
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(
...
- 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.
- 39
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/...
- 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 ...
- 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 ...
- 4,302
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 ...
- 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 ...
- 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 ...
- 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)::...
- 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)...
- 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:
...
- 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 ...
- 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 ...
- 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(...
- 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."...
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 ...
- 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.
- 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.
- 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 ...
- 3,180
Top 50 recent answers are included