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.
122
questions
3
votes
3
answers
101
views
Remove only certain squares from grid in PostGIS
So I have this grid (I create this from oriented enveloping the brown polygons and then buffering)
create temp table test_squares as select test_fishnet.poly from test_fishnet, polys where ...
- 161
3
votes
1
answer
134
views
In PostGIS, how to identify intersecting line segments, but not matching start and end nodes
I have a PostGIS table of thousands of linestring segments wherein each line segment has only two nodes, start and end. All of the lines have a start and/or end node that matches another line segment ...
- 1,349
4
votes
1
answer
50
views
PostGIS ST_Intersection with interpolated M value
I have a polygon and a GPS Trace (incl timestamps in the M dimension) zig-zagging across the polygon.
I want to find the time intervals whenever the GPS trace entered and exited the polygon, which can ...
- 83
1
vote
1
answer
66
views
Warnings() in functions sf package
I'm running the code below and I get several "warnings". I used the sf_use_s2(FALSE) function to work with plane coordinates following the old way of the 'sf' package but even so I keep ...
3
votes
1
answer
104
views
Recursive intersection search to discover paths over a large polygon dataset
Summary
I have a PostgreSQL table of some 80M polygons, and I am attempting to trace paths of connectivity from known starting points within areas of interest. The data is three-dimensional-ish, ...
- 59
0
votes
2
answers
118
views
Why doesn't ST_Intersects work with multiple rows of geom
I am new to PostGIS. I am trying to find the conditional intersection between two multi-polygons PostGIS tables (tab1 and tab2) when tab2 meets certain criteria (defined by row values of one of the ...
- 405
4
votes
1
answer
143
views
PostGIS order intersections of line and polygon
I have a polygon and a line that goes through it. That line might exit my polygon and enter it again at a later point.
So now I'd like to have a relative position inside my line, where those segments ...
- 921
3
votes
2
answers
130
views
Efficient combination of st_intersects and not st_touches
I'm attempting to an efficient way to find "overlapping" polygons. Specifically we have a bunch of postal code/city/neighborhood data where I specifically don't want the default behavior of ...
4
votes
1
answer
250
views
Unsplitting/merging lines that have coincident endpoints using PostGIS
I have a large dataset of lines/linestrings (ie. a road network), which is unregularly segmentised/ split and the lines needs to be merged at coincident endpoints (if only two lines are touching). I'd ...
- 153
5
votes
1
answer
254
views
Counting geometries which intersect another geometry using PostGIS
I'm trying to calculate the total area for each layer I have within a specific area and count the geometries which intersects with that specific area.
I can calculate the area and the results seem ...
- 391
4
votes
1
answer
169
views
Intersect polygons returns true instead of geometry
I try to do a simple cut between two layers and PostGIS returns a boolean instead of a geometry
select
t1.id,
st_intersects(t1.geom, t2.geom) geom
from
public.table1 t1
inner join
...
- 53
0
votes
0
answers
38
views
STBuffer is returning wrong values
I am trying to get nearest point from the given coordinates within 1km buffer in SQL.
Below is the code which I am using to get nearest point
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('...
- 345
0
votes
1
answer
174
views
PostGIS ST_Intersect - dealing with different coordinate systems
I try to get an intersection of two tables - lines and polygons. Lines' CRS is 3006 (SWEREF99 TM), while polygons' CRS is 4326 (WGS84). I write the query:
SELECT v.subid
FROM line AS v, polygon AS o
...
- 356
5
votes
1
answer
112
views
Applying ST_Intersects in a query with PostGIS
I have a PostgreSQL/PostGIS materialized view (study_region_all) with stream lines and a table of county boundary polygons (counties). They both have a column called co_fips but for a subset of items ...
- 1,679
0
votes
0
answers
182
views
Super-efficient geo-intersection operations in Python
I am working with big spatial data. I have about 2.3 million multilinestrings (driven routes) and I seek to count the number of intersections of each individual multilinestring with a set of ~60-150 ...
- 193
4
votes
1
answer
126
views
ST_Intersects with the whole world doesn't bring back all results
I have a table of devices with a column last_known_coordinates (geography(Point,4326)).
I want to find devices within a bounding box with a query. Some bounding boxes are working, but others are not - ...
- 113
3
votes
0
answers
43
views
PostGIS ST_Intersects giving unexpected results
I have a table of devices with a column last_known_coordinates (geography(Point,4326)). Here is an example last_known_coordinates:
0101000020E6100000569C00000D99EDBFE006C60B33F54640
Which is in ...
- 113
6
votes
2
answers
415
views
PostGIS ST_Contains / ST_Intersect with one point envelope
With PostGIS I have to test wether a point is inside an envelope or not. Sometimes the envelope is constructed from a point and a few others next to it, and sometimes only from a single point, against ...
- 574
3
votes
0
answers
131
views
PostGIS Query using ST_Intersects not using spatial index
As code is better than 1000 words, here we go :
explain analyze SELECT DISTINCT(zones.office_id) AS office_id FROM "zones" JOIN offices ON zones.office_id = offices.id WHERE ST_Intersects('...
1
vote
0
answers
23
views
Calculating costs of a line geometry based on a different intersecting geometry
I have 2 tables, a route table with line geometry and a duct table also with line geometry. Basically I need to calculate the costs of the different routes based on their intersection with the duct ...
- 2,190
1
vote
0
answers
42
views
How to count points intersecting with the end of a multilinestring?
I need to count points from table1 that intersect with the lines in table2 which has about an equal split between linestrings and multilinestrings.
Intersect works fine, but the thing is that I need ...
- 2,190
3
votes
1
answer
4k
views
R: Using st_intersects to classify points inside, outside and within a buffer
I have two objects, a shapefile of location points, points, and a shapefile of polygons, world_buffer (both +proj=utm +datum=WGS84). The world_buffer is the world's coastlines, found here, with a 5 km ...
- 205
1
vote
1
answer
60
views
Inserting values in a table depending on spatial relation between objects in to tables
I have a new and empty column in a table and I want to insert values, using postgis depending on the spatial relation between the geometries in two tables.
I have one table (points) with benches and ...
- 389
4
votes
1
answer
252
views
How to optimize PostGIS Intersection Query
I've 2 sets of polygons in 2 tables and the sets overlap each other. For each polygon in set A, I get the ID of the polygon in set B that it overlaps the most.
This is the query:
SELECT DISTINCT ON (a....
- 41
1
vote
1
answer
90
views
Snapping end points of line to point using PostGIS
I have a problem with over 10k lines from linetable that do not end/start in a point from pointtable.
I need to get these lines snapped so I tried to intersect them by using st_snap
update linetable c ...
- 2,190
0
votes
1
answer
47
views
Wrong result when clip a raster with PostGIS
I'm newbie of GIS, I have my first project with GIS without experience before.
I want to clip a zone of raster for the calculation of NDVI index. It is ok when I did it with GDAL and TIFF file. But I ...
2
votes
1
answer
89
views
PostGIS: problem with point count result when they are over the polygon edge
I have a PostgreSQL 12 database with PostGIS (using EPSG 4326). There are some tables with millions of points, and others with polygons.
In general , I had no problems with the queries. However, I ...
- 21
3
votes
2
answers
251
views
Performance issues with PostGIS ST_Intersects
I have a couple of tables in PostGIS that are big (~4 million rows). The geometry columns in both the tables are indexed. But I find a huge difference in query times between ST_Within and ...
- 143
1
vote
0
answers
233
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 ...
- 1,106
0
votes
0
answers
108
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
...
- 141
1
vote
0
answers
202
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: ...
- 111
3
votes
2
answers
90
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 ...
- 61
2
votes
0
answers
402
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 ...
- 277
2
votes
1
answer
197
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 ...
- 71
3
votes
2
answers
499
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 ...
- 381
2
votes
1
answer
220
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'...
- 1,365
6
votes
1
answer
606
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 ...
- 71
1
vote
0
answers
95
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 ...
- 11
0
votes
1
answer
104
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 ...
- 133
7
votes
4
answers
689
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 ...
- 1,365
2
votes
1
answer
598
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 ...
- 23
3
votes
2
answers
1k
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 ...
- 31
5
votes
4
answers
2k
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 ...
- 111
3
votes
1
answer
451
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: ...
- 163
1
vote
0
answers
368
views
Speed up PostGIS raster query
I have a simple PostGIS table as follows:
\d rad;
Table "public.rad"
Column | Type | Collation | Nullable | Default ...
- 23
2
votes
1
answer
244
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 ...
- 89
0
votes
0
answers
41
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 ...
- 567
1
vote
1
answer
42
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 ...
- 405
2
votes
1
answer
517
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 ...
- 481
1
vote
0
answers
152
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'...
- 63