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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
21 views

Error in st_intersects() %>% as.numeric() : 'list' object cannot be coerced to type 'double'

I'm working with 2 shapefiles (sf) containing longitude and latitude for both schools locations (shape_schools) and parks locations (shape_parks). I also have a shapefile containing mutually exclusive ...
Sergio Daniel Ordonez Beltran's user avatar
2 votes
0 answers
78 views

Intersecting multiple overlaping polygon tables in PostGIS

I have a seven linestring tables that I buffered to create polygons of each line network. I then successively intersected each table (Figure 1 below). I use the following sql query, but it take 36 ...
MJM's user avatar
  • 781
1 vote
0 answers
19 views

Mixed results of the intersection of a line with a polygon in PostGIS

I have a line network and a some buffers. I would like to obtain the value of the buffers that the line intersects; however, I am getting mixed results that I am unsure how to interpret and solve the ...
MJM's user avatar
  • 781
1 vote
1 answer
36 views

How to get entity coordinates that are located inside an OSM administrative boundary?

I downloaded the administrative boundary of Germany from a website as *.geojson file. I imported this *.geojson file into PostgreSQL like this. ./apps/ogr2ogr -f "PostgreSQL" PG:"host=&...
dancesWithCycles's user avatar
1 vote
0 answers
35 views

ST_Intersects failed with "ERROR: GetGenericCacheCollection: Could not find upper context"

When I run this query SELECT ST_Intersects(ST_geomFROMTEXT('POLYGON((105.767292 35.923215,112.165502 37.269499,114.177842 33.956548,113.636058 30.975062,105.393203 30.531638,105.767292 35.923215))', ...
Junnan Han's user avatar
3 votes
2 answers
116 views

Improving ST_Intersects performance in PostGIS

I have the following query, but it takes a long time to run. I tried creating an index, but there hasn't been much improvement. Any suggestions? CREATE INDEX temp_clusters_geom_idx ON temp_clusters ...
user3222101's user avatar
3 votes
1 answer
63 views

Intersection of Linestring with Polygon causes column mismatch in PostGIS

I have the following sql code that intersects each linestring with a polygon. The problem is that when the linestring is wholly covered in the polygon the descriptor attribute of the polygon is put ...
MJM's user avatar
  • 781
1 vote
0 answers
91 views

Optimising ST_Intersects query with geometry and point

I'm trying to optimise the following SQL query, which checks a list of areas to see if the user is within one of them, based on their lat/long. The database is Aurora Postgres, the areas table has one ...
h1h1's user avatar
  • 111
1 vote
0 answers
37 views

Intersection of lines with polygons query returns 0 entries [closed]

I have the following query that intersects a linestring with a polygon, which used to return all the intersected and non-intersected lines, but the query returns zero entries successfully now. I'm at ...
MJM's user avatar
  • 781
3 votes
0 answers
157 views

PostGIS difference between two tables with polygon

I have two polygon tables. I try to remove portions but the end result is not acceptable. I took the time to look at different questions and answers about ST_Difference but I couldn't solve my problem....
Bak's user avatar
  • 131
0 votes
2 answers
193 views

Geoserver returns error when using CQL_FILTER=INTERSECTS(...) "ST_Intersects: Operation on mixed SRID geometries (Point, 3857) != (Polygon, 0)"

When I try to send request to geoserver with BBOX: request=GetFeature srsname=EPSG:3857 CQL_FILTER=BBOX(geom,5147051.465935892,7328515.141536634,5147051.465935892,7328515.141536634) everething is ...
Eugene Kuklin's user avatar
1 vote
1 answer
41 views

PostGIS cannot find rows in table 1 where linestring in table 2 intersects multipolygon in table 1

I am new to PostGIS. I have two tables, first is a table with polygons containing areas of municipalities, the second table is one with all my bike rides and has a column with linestrings in it. Both ...
Bob Voorneveld's user avatar
0 votes
0 answers
83 views

QGIS MSSQL communication link failure QODBC3 when performing STIntersects

I added a MSSQL spatial layer in QGIS and I created a trigger (INSTEAD OF INSERT) in order to auto fill some fields. The trigger works ok but when I try to execute an STIntersects I get the following ...
sotokan80's user avatar
3 votes
1 answer
140 views

Using ST_Intersects inside of an ST_Within

I have a PostgreSQL trigger function getting executed by QGIS. That is working fine. I am trying to modify my function to count the number of points that fall within the intersection (clipped) ...
Ryan Peel's user avatar
2 votes
2 answers
473 views

PostGIS query with ST_Intersects/ST_Contains is incredibly slow

I want to run a PostGIS query that selects roads (total dataset: 103 mio.) that are inside a buffer polygon. The subqueries as standalone are very fast, but when I add the ST_Contains or similar ...
Marlena 's user avatar
3 votes
2 answers
210 views

How get list of id's of intersecting of polygons in the same table by PostGIS?

I have a table looking this this id geom 1 POLYON.... 2 POLYON.... 3 POLYON.... 4 POLYON.... 5 POLYON.... 6 POLYON.... .... 300 POLYON.... Some (not all) of them ...
four-eyes's user avatar
  • 3,370
1 vote
1 answer
174 views

How to determine the intersection of a Raster and a point Shapefile? [closed]

I am new to PostGIS and I can't find a way to intersect my raster map with my points shapefile. The raster is the country and the shapefile is associated with the cities. I would like to determine the ...
UV-Sam's user avatar
  • 29
3 votes
3 answers
163 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 ...
tbob's user avatar
  • 584
3 votes
1 answer
324 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 ...
jamierob's user avatar
  • 1,519
4 votes
1 answer
257 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 ...
Christoph's user avatar
1 vote
1 answer
1k 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 ...
Caroline Portal's user avatar
3 votes
1 answer
334 views

Recursive intersection search to discover paths over a large polygon dataset [closed]

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, ...
AgentOddball's user avatar
0 votes
2 answers
487 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 ...
rm167's user avatar
  • 459
4 votes
1 answer
672 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 ...
Georg's user avatar
  • 991
6 votes
2 answers
722 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 ...
Tim Robertson's user avatar
5 votes
1 answer
845 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 ...
krenz's user avatar
  • 163
5 votes
1 answer
1k 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 ...
BR75's user avatar
  • 437
4 votes
1 answer
352 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 ...
SCH kiman's user avatar
0 votes
1 answer
599 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 ...
Elena Klimenko's user avatar
5 votes
1 answer
539 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 ...
gwydion93's user avatar
  • 1,873
0 votes
0 answers
818 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 ...
Ben's user avatar
  • 193
4 votes
1 answer
171 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 - ...
Tony's user avatar
  • 113
3 votes
0 answers
93 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 ...
Tony's user avatar
  • 113
6 votes
2 answers
1k 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 ...
Tim Autin's user avatar
  • 584
3 votes
0 answers
423 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('...
LondonUnderground's user avatar
1 vote
0 answers
29 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 ...
Luffydude's user avatar
  • 2,278
1 vote
0 answers
78 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 ...
Luffydude's user avatar
  • 2,278
5 votes
1 answer
13k 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 ...
cgxytf's user avatar
  • 225
1 vote
1 answer
138 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 ...
kwiwe's user avatar
  • 389
4 votes
1 answer
836 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....
Manu's user avatar
  • 41
1 vote
1 answer
193 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 ...
Luffydude's user avatar
  • 2,278
1 vote
1 answer
96 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 ...
Nguyen Duc Tien's user avatar
2 votes
1 answer
180 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 ...
Gustavo's user avatar
  • 21
3 votes
2 answers
545 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 ...
sridharraman's user avatar
1 vote
0 answers
491 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 ...
Pitrako Junior's user avatar
0 votes
0 answers
513 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 ...
A l w a y s S u n n y's user avatar
1 vote
0 answers
428 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: ...
Baschdl's user avatar
  • 111
3 votes
2 answers
189 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 ...
Michal DML's user avatar
2 votes
0 answers
743 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 ...
Lucas_Edenhofer's user avatar
3 votes
1 answer
391 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 ...
omar's user avatar
  • 111