Questions tagged [st-intersects]
ST_Intersects is a SQL function that takes two geometries and returns true if any part of those geometries is shared between them.
96
questions
0
votes
0answers
15 views
Adjusting polygons layers's boundaries in PostGIS [duplicate]
The case is quite typical. I’ve to compare two (multi-)polygons layers of administrative regions, with l2 being lower level (let’s say municipalities – 2811 records) than l1 (let’s say provinces – 785 ...
1
vote
0answers
21 views
Using spatial indexes on point to polygon intersection query in SQL Server
I have a table (tab_point) with about 5 million points and another table with a few thousand polygons (tab_polyg). I need to find what points intersect with what polygon (to establish a count of ...
0
votes
0answers
28 views
PostGIS ST_Intersects or ST_Within for Point in Boundary
When I query with ST_Intersects or ST_Within I get the same result. I want to know which one to use for performance wise? Note: For points, there is no difference.
Using ST_Within:
EXPLAIN ANALYZE
...
1
vote
0answers
36 views
PostGIS: Speed up intersect by filtering first
I have a table with 20 million polygons where queries take some time (with an gist index on the geometries). I thought I could speed up the queries by first filtering on an additional id (selectivity: ...
3
votes
2answers
64 views
How to get the area of two interesecting polygons divided into attributes on PostGIS
I am trying to calculate the area overlap two vector polygon layers.
I have the one based layer - soil map and I have second layer - plots.
The example below:
I would like to know how much area each ...
2
votes
0answers
102 views
Create spatial joined table with intersected geometry using ST_Intersects and ST_Intersection in PostGIS
I want to perform a spatial intersection join of the two tables "temperature_precipation_slope" and "buek200_clc10 with ST_Intersects in PostGIS via psycopg2 in Python and write the ...
2
votes
1answer
84 views
ST_Intersects slow across two tables
I have a set of municipality maps, with an ID for each municipality, and a Multipolygon for the geometry. In PostGIS, I've put these data into tables. Table 1 has the municipalities in year 2019 (each ...
0
votes
0answers
22 views
Using St_Intersects to find roads crossed on a journey
Is it feasible to make a query on a spatial database to find out how many main roads need to be crossed on a journey between two point features? I understand there is a ST_Intersects function but can ...
3
votes
2answers
245 views
PostGIS Spatial Relationships query performance
I have a very large dataset contains over 700 million points and a polygon dataset as a buffer zone.
My task is to extract all points inside the buffer zone and create a new table.
Below is my code. I ...
2
votes
1answer
63 views
Find COUNT of multiple tables that ST_INTERSECT a main table
I have 3 tables. 1 polygon main table polytable (400,000 polys with 1000 unique names in the polyname column), and 2 point tables pointtable1 (millions of points) and pointtable2 (1000s of points). I'...
6
votes
1answer
246 views
PostGIS ST_Intersects does not return expected result
I want to search objects that have at least one point inside of a rectangle. Based on the size of the rectangle, PostGIS sometimes returns the contained object, sometimes not.
create table t1 (id ...
1
vote
0answers
62 views
Efficient Way of Getting Intersections Between Segments and Shapes in Postgis
I have a set of 7849 tridimensional segments and 71 tridimensional shapes. My goal is to count the number of intersections between the segments and the shapes using Postgis. The segments were modeled ...
0
votes
1answer
55 views
Linestring table (of MultiLinestrings) ST_Intersection
I'm moving from GeoPandas to PostGIS and GeoAlchemy.
I've a table full of multilinestring geometries (timezones from NaturalEarth.com) and I've a linestring which represents what will be the route of ...
7
votes
4answers
495 views
Count number of points by name within distinct polygons [closed]
In PostgreSQL/PostGIS I have 2 tables:
A POLYGON table (polytable1) with shapes named 'Poly 1', 'Poly 2' etc... Each name can appear multiple times (i.e. 20 polygons with the name 'Poly 1')
A POINT ...
2
votes
1answer
145 views
ST_Intersects with multiple geometries
I am just learning PostGIS and have a simple problem I can't get the answer for.
I want to select the geometries that intersect with given multiple geometries.
To make it simpler, let's say I want ...
3
votes
2answers
612 views
Combining ST Buffer and Intersect with Where clause
I'm trying to create a buffer around a single point in a spatial entity and finding all the points from a different entity that fall within that buffer.
I tried two approaches. The code below runs ...
5
votes
4answers
869 views
Finding truly intersected polygons
Intro
Short version: My goal is to find geometries intersected by a target polygon.
To find overlaps - the most straightforward choice is to use ST_Overlaps or ST_Intersects. But this method will ...
2
votes
1answer
207 views
Why do two tables with valid geometry return self-intersection errors when intersected?
I have two tables A and B, both have valid geometries when I check with ST_IsValid. In my query, I intersect the two and the query stops in the middle because of this error:
ERROR: ...
1
vote
0answers
192 views
Speed up PostGIS raster query
I have a simple PostGIS table as follows:
\d rad;
Table "public.rad"
Column | Type | Collation | Nullable | Default ...
2
votes
1answer
133 views
Optimising Large Spatial Queries in SQL
I'm sure similar answers have been given elsewhere but no matter what I have tried I am unable to optimise this query.
Details: TableA has 3.5mill records TableB Had 57000 records but I have ...
0
votes
0answers
36 views
PostGIS - Tables UNION
I have three tables (Table_1, Table_2, Table_3) with the same fields:
geom, Field_a, Field_b, Field_c, Field_d.
I want to SELECT these tables so that all their rows appear but I want do not want rows ...
1
vote
1answer
34 views
Fails ST_intersects(geom, select statement) for drive time
I am trying to select houses in a drive time isochrone.
I have the code working fine to make the isochrone.
I can't place the results in a seperate table
I need the isochrone in a where clause
Is ...
2
votes
1answer
311 views
ST_Intersection + ST_Difference + not overlapping Polygons
I have a PostGIS table with some overlapping polygon and need a PostGIS view where no polygon is overlapping and the data is aggregated (like in the picture).
With a Union of ST_Intersection() and ...
1
vote
0answers
81 views
Optimize a query with st_intersect()
I have three tables containing increasingly-complex geometry. I'm clipping Tables 2 and 3 to the first, least-complex table (with 700 multipolygons). The other two tables contain spatial attributes I'...
2
votes
0answers
100 views
Behaviour ST_Intersection of 3D LINESTRING and 3D POLYGON - Z value with GEOS backend
I do not quite understand the behaviour of ST_Intersection applied to 3D geomerties when using the GEOS backend. Take this example:
SELECT ST_AsText(ST_Intersection('POLYGON((1 1 1, 6 1 3, 6 4 3, 1 4 ...
1
vote
1answer
123 views
PostGIS: Running multiple intersections within a single SQL query?
PostgreSQL 11 + PostGIS, on Windows 10.
I'm working with a PostGIS-enabled database.
I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the ...
2
votes
1answer
515 views
Getting “missing FROM-clause” error when adding ST_Intersects constraint to PostGIS table
I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added.
Obviously dams have to cross a river ...
1
vote
0answers
188 views
Uncompressed vs Subdivided: which one offers better PostGIS INTERSECTS performance?
In my PostGIS I have a point table (roughly 51 millions rows) and a multipolygon table. I need to find a polygon label/ID for every single point in my point table (roughly 51 millions rows) in PostGIS....
2
votes
2answers
492 views
Spatial joining two line layers and checking intersection in PostgreSQL/PostGIS
I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?...
2
votes
1answer
182 views
Find a point in a multipolygon geometry
I need to know which points from Table 1 are contained in Table 2.
In Table 1 I have longitude and latitude and I made points with ST_SetSRID(ST_MakePoint(d.longitude, d.latitude),4326) as geom
...
2
votes
2answers
116 views
write z-value from polygon layer to point layer in QGIS
I have two layers:
1 polygon layer where the depth of an area is written in the database (as DRVAL1) and and where the depth is exported as a Z-value to the geometry.
1 point layer that I use to plan ...
1
vote
1answer
126 views
Efficiently query two properties QGIS spatialite
I have a simple query that involves two steps:
non-spatial criteria (should be super-fast)
spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (...
0
votes
1answer
784 views
MSSQL performance with Geography (point within polygon)
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a ...
3
votes
2answers
543 views
Find what polygons are not fully covered by union of polygons from another layer in PostGIS
I have two tables in a PostGIS database
>>> select postgis_version();
"2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
One represents the country borders and another one is administrative ...
2
votes
2answers
172 views
PostGIS intersects and case function
Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons ...
1
vote
1answer
169 views
ST_Intersects one polygon with layer - always returns 0 results
I'm trying to write a query to find if any features in a layer intersect with a certain polygon, but it is always returning 0 rows. I've tried messing with SRIDs, different geom types, sizes, shapes, ...
0
votes
1answer
35 views
Intersect lines and polygons [closed]
I am tring to intersect lines with a polygon grid, but i got an error when i run this:
create table "st11_01_vd" as
SELECT (
"grid_1km.gid",
"st11_012017_ln.linetime",
ST_INTERSECTION("...
1
vote
1answer
178 views
PostGIS ST_Intersects result is ignored by where condition
I have a simple query filtering by st_intersects function finding all lines intersecting with a polygon.
select *, st_intersect(graph_line, st_geomfromtext('POLYGON((261 156,148 452,333 507,566 199,...
1
vote
1answer
682 views
Efficient intersect query for virtual layers in QGIS
I have 3 tables/shapes as follows:
ADRR (adress points)
ADRR_ID | ST_ID (street surface id) | point geometry
STREET
ST_ID | ST_NAME (street name) | polygon geometry
Obstacles
ID_OBS | ...
1
vote
1answer
439 views
Subquery inside ST_Intersects
I have 3 geometry tables a, b and c with geometry column geom
What I want is to first check if geom of b and c, intersects under some given condition using a subquery, and feed the subquery geometry ...
2
votes
1answer
649 views
Slow PostGIS line intersection even with indexes
I have line data contained in different graphs. Querying data over multiple graphs and using st_intersects results in slow queries. All lines are represented as geometry(LineString).
I am querying ...
4
votes
1answer
127 views
Postgis spatial index not being used
I have a query to calculate how many crimes (of a certain type) are rural and urban:
select st_intersects(c.wkb_geometry,u.wkb_geometry) as ur,count(c.*)
from crime.street_crime c, meridian.urban ...
2
votes
0answers
78 views
postgis and st_intersects ; fail to compare a table to itself with a left join
postgresql 9.6, postgis 2.3
Following this Select features that do NOT intersect in PostGIS, I would like to compare one table to itself, to keep all records from table p1, even and above all those ...
5
votes
2answers
223 views
Postgres/PostGIS skip intersect on features already intersected
I have a collection of polygons(building outlines called "Build_poly") that I am intersecting with another table of flood defenses (also polygons called Flood_poly).
When ever one of the building ...
0
votes
0answers
146 views
OGR SQL Intersect layer slow to iterate
I am having an issue with OGR's ExecuteSQL, but not with the actual SQL query.
What I am intending to do is to take a large number of polygons ~500k and find out if they intersect with a ...
5
votes
1answer
282 views
SQL / PostGIS: Expanding polygons contained inside another polygon until one vertice touches bounding box
We have a problem that we are not quite sure how to approach, we have a number of polygons contained inside a larger bounding polygon, We need to expand these internal polygons as much as possible but ...
0
votes
2answers
680 views
ST_Intersection not returning all intersecting geometries
I have a query that will calculate the proportions of given columns based on the area of intersection. The returns mostly the polygons entirely inside the other polygon, and in some cases returns the ...
0
votes
0answers
28 views
MultiPolygon Intersect only working for first Polygon in collection, how can I fix this?
I am very new to PostGIS.
I have a table called regions, that has name, an id and a geojson that is a MultiPolygon.
Now I need to check whether certain points or other MultiPolygon regions intersect ...
1
vote
1answer
66 views
ST_Overlaps and related
I have a table with ~15K records consisting in points with a 200m buffer and I need to investigate how many of them are in proximity of polygons contained in another table (~20K).
I wrote a function (...
1
vote
1answer
561 views
Removing duplicate rows created by ST_Intersects in PostGIS?
I want to basically take one spatial table of polygons and determine if any of the polygons interest with another table of polygons and save the results in a new table with a new column saving the ...
