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.
138
questions
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 ...
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 ...
- 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 ...
- 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=&...
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))', ...
- 19
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
...
- 131
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 ...
- 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 ...
- 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 ...
- 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....
- 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 ...
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 ...
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 ...
- 73
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) ...
- 31
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 ...
- 21
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 83
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 ...
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, ...
- 57
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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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
...
- 53
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
...
- 356
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 ...
- 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 ...
- 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 - ...
- 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 ...
- 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 ...
- 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('...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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....
- 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 ...
- 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 ...
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 ...
- 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 ...
- 143
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 ...
- 1,206
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
...
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: ...
- 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 ...
- 61
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 ...
- 247
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 ...
- 111