Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

Filter by
Sorted by
Tagged with
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 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(...
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 ...
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