Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,984
questions
1
vote
1
answer
55
views
Randomize the sf::st_point_surface() points placement within a polygon
I have a dataset of our clients across the country (more than 100k rows). For each client, we know their county (polygon) but I would like to represent them with points instead.
I want to place the ...
4
votes
0
answers
68
views
Approximate ST_ShortestLine in geography (PostGIS)
Actually PostGIS has a function to get the shortest line:
https://postgis.net/docs/ST_ShortestLine.html
But as you can see, the support is only for geometry type, so we can't get the shortest line for ...
- 61
0
votes
0
answers
31
views
Changing attributes on a feature layer in offline project QGIS (working in PostGIS), then after synchronization the attributes have lost their changes
I have an offline project in QGIS as we are working with PostGIS layers. I have a .shp and I want it to be merged with a feature layer in my PostGIS. So, I upload the .shp in my pgAdmin, create a ...
- 1
0
votes
2
answers
69
views
PostGIS docker image giving different result than local PostGIS (Windows 10)
I've got a monster of a query that at the end will spit out some lines (4326) and before the final lines are spit out, the ones that run into each other are combined. This works great locally;
I ...
- 526
0
votes
0
answers
69
views
GeoServer generated Image Mosaic properties missing TimeAttribute
I am using an ImageMosaic store with a granule index stored in a PostgreSQL database. Adding granules and updating the index is done manually and not through GeoServer. GeoServer is accessing the ...
- 11
4
votes
1
answer
194
views
Converting to EPSG code gives error
My underlying EPSG is 4326.
When I try the following code:
ST_ClusterDBSCAN(st_transform(points_geom,9234)
I receive an error:
SQL Error [XX000]: ERROR: Cannot find SRID (9234) in spatial_ref_sys
...
- 557
1
vote
1
answer
38
views
PostGIS: given two points and the max distance in meters find if the two points are within that distance
Given latitude and longitide of two points I have to find if them are withing a given max distance in meter, I found this function:
https://postgis.net/docs/ST_DWithin.html
and I am using it in this ...
- 113
0
votes
1
answer
40
views
Multiple transformations within a single projection on epsg.io
I am referring to https://epsg.io/24313
Here, EPSG:24313 is written in bold at the top of the page, and then there are multiple subprojections listed underneath. I am confused about which one to use, ...
- 557
1
vote
2
answers
73
views
Make radial lines that run in and join each other for grid cells PostGIS
So I have this grid
and I want to make radial lines that come out of the centroid of each of the grid cells like spokes. lets say I want there to be 8 spokes. The spokes from one grid cell should ...
- 526
1
vote
1
answer
35
views
Unit of measure for DBSCAN
My code is:
ST_ClusterDBSCAN(st_transform(points_geom,5243),eps := 50, minPoints := 100)
The underlying EPSG was 4326 previously.
Am I right in assuming that the DBSCAN now is using meters as its ...
- 557
2
votes
1
answer
119
views
PostgreSQL Create A Line Column From Two Point Columns
My database has beginning and end points [beginning(x,y) ending(x,y)]. I want to create lines from this database for each beginning-end points.
I tried ST_MAKELINE but this function wants location as ...
- 23
1
vote
2
answers
43
views
How to create grid in 4326 just like ST_CreateFishnet PostGIS
So I've been projecting my geometries to 102008, then using this function:
CREATE OR REPLACE FUNCTION ST_CreateFishnet(
nrow integer, ncol integer,
xsize ...
- 526
0
votes
1
answer
45
views
how to store a road with multiple routes?
I assume everybody knows google maps. When you get any direction to you destination there is always mutliple options. Let's say I want to store such a geometry in PostGIS? What is the best approach to ...
- 1
5
votes
1
answer
143
views
Understanding vector re-projection process/steps
I am trying to understand the vector re projection on QGIS/PostGIS.
I want to view the table that shows interim projection system when a layer is projected from one projection to another. Its a table ...
1
vote
1
answer
37
views
How to edit primary keys from shp2pgsql-gui output?
I have a newbie question. I am importing multiple shapefiles into shp2pgsql-gui and creating a postgis database. While creating the database, shp2pgsql adds a new gid column for each layer that ...
- 11
2
votes
2
answers
170
views
Using PostGIS to break up large polygon into smaller segments
I have a large complicated polygon that looks like this:
I want to brake this up into many smaller segments no more than about 100x100 meters in size each. How can do this? I have tried st_segmentize(...
- 526
3
votes
0
answers
77
views
Point outside of projection domain (2050) Error
I have an intermittent issue with my application stemming from a PostGIS error.
Randomly during normal operations (sometimes weeks at a time without issue), every ST_Buffer invocation results in the ...
- 31
7
votes
2
answers
251
views
ST_Difference(A, B) returning A when A completely contained in B [closed]
I'm trying to delete a line string using a polygon via ST_Difference. I start with the line string, but when I difference it with a surrounding box, I just get the original line string back:
SELECT
...
- 73
1
vote
0
answers
32
views
QGIS using one to may relation data for layer label trouble
For the first time I tried to set up a one to many relationship in QGIS.24 using Postgis spatial table and a view. The view includes a foreign field key referring to the table's primary key.
CREATE ...
- 243
1
vote
1
answer
32
views
Union two geometries from the same table, in the same table, keeping information of only one record
I have a table with several records. I want to union the geometries of two of the records, preserving the information from the other columns from only one record.
id col1 col2 col3 geom
1 A AA ...
- 3,227
0
votes
0
answers
27
views
Qgis 3 & table PostGIS : how to prevent/disabled save as (basic users) [duplicate]
With PostGIS and QGIS 3 (Windows), what ways can deactivate/prevent for simple PostGIS users (PostgreSQL connection role only SELECT rights on tables), the functionality offered by QGIS to export from ...
- 821
3
votes
0
answers
81
views
Does GeoPandas generate a PostGIS spatial index automatically?
It is easy to save a GeoPandas dataframe to a PostGIS database using the gdf.to_postgis(...) command. But, it is not clear to me if it automatically generates spatial indices or if these need to be ...
- 327
7
votes
1
answer
196
views
Find discontinuities in graph network
I have a layer of lines that construct a graph network like the one shown in the 1st figure.
I need to automatically find the places in my network where there are discontinuities, like the ones marked ...
- 329
2
votes
1
answer
50
views
geometry vs geography for distance measurement and large dataset
I'm working on a project where I have clusters of GPS coordinates to store, let's say ~20k points per cluster. The clusters overlay with big cities, like London or New York. I have many clusters ...
- 123
0
votes
1
answer
54
views
Get all paths between the source and destination nodes in PostGIS using PGRouting extension
How to get all paths between the source and destination nodes in PostGIS using PGRouting extension?
I tried the functions:
pgr_edgeDisjointPaths: It returns only the Disjoint paths.([40,41,42,45],[40,...
0
votes
0
answers
64
views
Convert data to lat lng coordinates in looker
dimension: bounds {
type: location
sql_latitude: ST_Y(ST_Centroid(ST_Transform(${TABLE}."bounds", 4326)));;
sql_longitude: ST_X(ST_Centroid(ST_Transform(${TABLE}."bounds&...
- 1
1
vote
1
answer
32
views
Transforming a linestring table to trajectories on the fly
I have the following code:
tracks AS
(SELECT
imei,
st_setsrid(ST_MakeLine(points_geom ORDER BY time_created),4326) AS track from
subset_cr
GROUP BY imei
)
Is there any way I can ...
- 557
0
votes
0
answers
34
views
DBSCAN vs self implemented clustering function
In your experience, would a minimal code (because I am not very experienced with PostGIS)- self implemented clustering function be more accurate (and fast) than DBSCAN? For some reason DBSCAN is not ...
- 557
0
votes
1
answer
32
views
Calculating distance in metres
Here is my code, the CRS is EPSG 4326:
st_distance(
st_transform(routes.points_geom, 4326)::geography ,
st_transform(cg.cluster_centroid, 4326)::geography )
Am I correct in assuming that this ...
- 557
1
vote
1
answer
25
views
Serving big timeseries multidimensional vector grid
I've been working on creating a timeseries data aggregation of ocean vessel data. Things like the number of seconds that vessels spent in each grid cell per day, per vessel class, per navigation ...
- 1,336
0
votes
0
answers
20
views
Slow upload time in PostGIS [duplicate]
I have a csv file of 144 millions different points with latitude and longitude values. Using the following SQL statement, it currently takes me more than 24 hours to upload the data:
CREATE TABLE pts(...
- 343
0
votes
0
answers
38
views
ST_Union on multiple attributes gone bad
My server runs a daily script that takes polygons and lines from a set of *.000 cells, initiated by a cronjob.
When the first step (ogr2ogr) finished I have a layer that shows the original boundries ...
- 914
2
votes
1
answer
105
views
BigQuery ST_MakeLine vs PostGIS ST_MakeLine
Description
I'm trying to move our database from BigQuery to PostgreSQL. The issue that I'm encountering is that calculated distance for a line, built from same GPS traces, are way different in ...
- 121
1
vote
0
answers
47
views
Understanding if Cross Join Lateral is working as expected
I have two CTEs:
cluster_groups (cluster_id [int], cluster_centroid [points_geom])
subset_cr (imei [identifier], points_geom [geometry], time_created )
This is my code:
SELECT
cg....
- 557
0
votes
0
answers
25
views
ST_Clip leaves an empty 1-pixel wide column
I have an input raster and I want to cut out a small piece from the center. I do ST_Clip and I get the cut, but I also get a 1-pixel wide row and column of transparent pixels. What's wrong?
The ...
- 123
0
votes
1
answer
34
views
Obtain the time when a linestring track leaves a defined radius around a point
I have a table of linestring tracks as:
tracks AS
(SELECT
imei,
st_setsrid(ST_MakeLine(points_geom ORDER BY time_created),4326) AS track from
subset_cr
GROUP BY imei
)
I also have ...
- 557
0
votes
0
answers
104
views
How to load spatial data from PostgreSQL to Visual Studio Window Form
I am a beginner in Visual Studio.
I want to load spatial data from PostgreSQL to a GMap Windows form. I have no idea how to load that data, though now I can add normal coordinates direct (longitude ...
- 1
0
votes
1
answer
83
views
ST_ConcaveHull() is giving strange results in some specific cases
I'm playing around with ST_ConcaveHull() (baked on top of GEOS) in the hope of solving a small PostGIS problem that I have.
I'll got straight to the point.
This is working:
SELECT
ST_ConcaveHull(
...
- 8,811
0
votes
1
answer
33
views
Sew lines with tiny deviations due to numerical inaccuracy to construct a concave shaped polygon
Context
I initially had a PostGIS problem when it comes to closing a globally V-shaped multi-line (having some local concavities) into a polygon. See here: Closing a concave V-shaped MultiLineString ...
- 8,811
4
votes
1
answer
99
views
Is "order by" clause in DBSCAN necessary?
In the following code, is "order by" necessary for the DBScan function or is it adding an overhead for no reason?
clustering as(
SELECT row_id, city_id , imei, speed, points_geom ,...
- 557
1
vote
1
answer
48
views
Copy/Paste of features into existing PostGIS table
I am trying to copy and paste point features from one table to an existing PostGIS table, but I get a NOT NULL, Unique error for the id field (below). How can I update the PostGIS table with new data ...
- 277
1
vote
0
answers
27
views
ERROR: GEOSIntersects - Calculating intersection of tracks and buffered tracks
I am using the following code to calculate intersections between tracks and buffered tracks:
tracks AS
(SELECT
imei,
st_setsrid(ST_MakeLine(points_geom ORDER BY time_created),4326) AS track ...
- 557
3
votes
2
answers
144
views
using st_collect vs st_union to calculate centroids
Here is my code:
SELECT cluster_id, st_centroid(st_union(points_geom)) as cluster_centroid
FROM clustering
GROUP by cluster_id
Should I replace st_union with st_collect? Because the coordinates for ...
- 557
1
vote
0
answers
32
views
Calculating speed at points via window functions
I want to calculate speed at every point for each device_id, such that there is a new column of "speed" attached to the current dataset
My dataset is like:
imei(device_id) point_geom(...
- 557
2
votes
1
answer
24
views
Calculating speed at various points along a route for each device
I want to calculate speed at every point for each device_id, such that there is a new column of "speed" is attached to the current dataset
My dataset is like
imei(device_id) ...
- 557
1
vote
2
answers
66
views
Best approximation of meters to degrees - North America
I am working with the st_dwthin with srid 4326 in North America.
I would like to write down the distance_of_srid in meters.
Which function or method will be the most accurate?
- 111
1
vote
0
answers
14
views
Cross join lateral for radius bounded Multi-linestrings
Within a 500m radius of each centroid, I am trying to find out the total length of all multi-linestrings.
Am I doing this correctly? The values for the final sum are too low to be plausible.
Please ...
- 557
0
votes
0
answers
36
views
Extract total length all linestrings within a certain radius of a point_geom column (not fixed point) [duplicate]
I have the total intersecting tracks given by the following code:
intersections_cte as(
select imei, intimei, st_length(st_union(intgeom)::geography) intlength, st_union(intgeom) as ...
- 557
3
votes
2
answers
136
views
Total length of intersecting lines - comparing multiple lines to each other
I have a tracks table as such:
tracks AS
(SELECT
imei,
ST_MakeLine(points_geom ORDER BY time_created) AS track from
dataset
GROUP BY imei
)
imei is the device id /
points geom has ...
- 557
2
votes
1
answer
37
views
Total number of intersections of multiple linestrings - not working as expected
I have a table of gps tracks as follows (points_geom is SRID 4326) -
tracks AS
(SELECT
imei,
ST_MakeLine(points_geom ORDER BY time_created) AS track from
dataset
GROUP BY imei
)
...
- 557