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
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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('...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 - ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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('...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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: ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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'...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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: ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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'...
user avatar
  • 63