Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
3
votes
1answer
505 views

Use NTv2 grid files in PostGIS ST_Transform

This question is related to How to use gridfiles (*.gsb) in QGIS Situation is the following: The nationwide standardized gridfile for the transformation of geotopography data BeTA2007 is long since ...
5
votes
1answer
336 views

PostGIS - unique constraint for spatial equality

I have a polygon column for which I would like to add a unique constraint that tests for spatial equality akin to ST_Equals so that it ignores vertex order. For instance, I would like these polygons ...
2
votes
1answer
222 views

Trigger PostGIS QGIS

I have 2 layers on PostGIS: a point layer named 'tdia' and a polygon layer named 'com': -the com layer contains the city name. (Polygon) -the tdia layer contains bus stops. (point) I want to create a ...
4
votes
1answer
238 views

Using ST_Segmentize to PostgreSQL table of segments?

I have a PostgreSQL table with straight lines (segments) that I need to subdivide in further segments so that none of them is more than 50 metres long. I am new using PostGIS (and SQL) and I'm ...
4
votes
1answer
62 views

Storing Central European data, which SRS/which data type?

I am creating a PostGIS 2.X data base to store some 500 or so sites as points in it, which are distributed in the northeastern part of Germany, northern Poland and parts of the southern Baltic coast. ...
2
votes
2answers
338 views

Spatial joining two line layers and checking intersection in PostgreSQL/PostGIS

I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?...
4
votes
3answers
185 views

How to group by / Order by with logical sequence

I have a problem in SQL (QGIS). I have a shape with plots. I would like to obtain a certain organisation. Here a schema of what I have and what I want :
5
votes
1answer
779 views

Automatically Snapping Points to Closest Part of Line

I have some line points representing manholes. I also have line features representing roads. Both of these features are currently living in a PostGIS database. I'd like to automatically move these ...
2
votes
2answers
683 views

Nearest Neighbor

We've got a table of approximately 300,000 points and would like to find the nearest neighbor (within 10km) to each point in the same table. The most efficient query we've found so far is below, but ...
4
votes
1answer
117 views

raster2pgsql not in C:\Program Files\PostgreSQL\12\bin

After adding both C:\Program Files\PostgreSQL\12\bin C:\Program Files\PostgreSQL\12\lib to my windows environmental variables PATH thing, I can't access raster2pgsql: 'raster2pgsql' is not ...
0
votes
0answers
21 views

Connection between GeoNetwork and PostgreSQL

I want to migrate to GeoNetwork 3.10.2 from 3.8 by following those instructions : BACKUP my DB PostgreSQL I change these files in order to point my new DB C:\Program Files\Apache Software Foundation\...
2
votes
1answer
74 views

How to estimate time spent within a location using GPS/timestamp data in PostGIS?

I am new to PostGIS, and I am trying to estimate time spent in a location based off of interpolating time between GPS points (I am using PostgreSQL 12.2 and PostGIS 3.0). I have GPS points stored ...
0
votes
1answer
2k views

ST_Area and SRID 4326

I'm working in the SRID 4326 system and need to compute the area of rectilinear polygons. Our geometry (geom) column is in WKB format When I execute the ST_Area function it appears my results are ...
0
votes
1answer
42 views

How to update tables from QGIS to pgAdmin?

I have created a connection between QGIS (ver. 3.10.1-A Coruña) and PostgreSQL (pgAdmin 4). Every time I edit/update my table in QGIS I have to delete the table in PostgreSQL and re-import again the ...
3
votes
2answers
122 views

Update points geometry while snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance. By now I managed to create a new table ...
2
votes
2answers
371 views

Can QGIS manage non-spatial PostgreSQL database?

I have a database with a table with plots. This table has a spatial field for the area. Also, I have another table with the owners. An owner can own some plots, and there is an "id_owner" field in the ...
3
votes
1answer
1k views

Calculating total line lengths within polygon?

I have basically the same conundrum as Calculating line segment lengths within polygon using ArcGIS Desktop? but I'm working in PostGIS, not ArcGIS. Any pointers? This query seems to like it should ...
1
vote
1answer
349 views

PostgreSQL-12.3 / PostGIS-3.0 - could not access file “$libdir/postgis-2.4”: No such file or directory

I am aware there are a few similar questions to this one, such as here and here. My problem is however a bit different. I've recently upgraded my Ubuntu to 20.04 and there only PostgreSQL 12.3 and ...
1
vote
1answer
41 views

Why does my source and target repeats itself when I use pgr_createtopology()?

I created my path in QGIS v2.18.18, making the lines and a combination of cleaning tool and explode lines tool to get my desired output. I tried using ROAD GRAPH plugin in order to check whether my ...
2
votes
0answers
40 views

pgRouting: pgr_KSP() does not return shortest path when k > 1

I am trying to get an arbitrary number of paths between two points in a network. I am using pgr_KSP — Returns the “K” shortest paths. My aim is to get the K cheapest routes between two points. ...
0
votes
0answers
22 views

WFS Request against a layer with a foreign table join

I have a Geoserver layer that is pulling from a Postgresql view that contains a left join to a foreign table (data from a SQL Server database). If I query against the view in PGAdmin (ie. SELECT * ...
1
vote
0answers
26 views

How to extract lat, long coordinates from Geography type column in dataframe using SQLAlchemy?

This is the SQL command that works perfectly. SELECT lonlat, ST_Y(lonlat::geometry) AS latitude, ST_X(lonlat::geometry) AS longitude This is the output of the SQL command. lonlat ...
3
votes
2answers
31 views

Postgres: Remove Duplicate Rows returned by ST_Dwithin query

I am having trouble finding a query that returns one row for every unique vehicle crash that is within 100 feet of a select group of road features. My query currently returns a row for every collision ...
2
votes
1answer
48 views

Performance optimization of ST_Touches

The query I am running is taking a considerable amount of time. I was wondering whether I am missing an opportunity to drastically improve performance. My goal is to find out how many neighboring ...
0
votes
2answers
39 views

Geometry in PostGIS Table wrong SRID and type [closed]

I have loaded some points into a table as Multipoint, they have come through with an SRID of 0. I need to change these to have a SRID of 7844 and also change them to be of type Point. There are other ...
1
vote
1answer
86 views

Clean the merge from some features

I have a layer of pipes,water conductors, that I merged under the condition of having the same material and they're touching each other, it did a good work but some features which have the same ...
3
votes
1answer
179 views

PostGIS Zonal statistic (St_SummaryStats()) NULL problem

I try to assign each Polygon in my data set the maximum value that can be found within their areas. I have a raster and a collection of Polygons with each have a unique id (gid). I uploaded the ...
2
votes
1answer
46 views

PostGIS ST_Union with IF statement

I have a dataset containing buildings where each building has a certain value/score. What I want to do is make clusters of these buildings depending on their values/scores. What I did so far is; I ...
0
votes
1answer
33 views

How to create geometry from set or array of lat/lon in table using PostGIS?

My question is similar to Creating geometry from lat/lon in table using PostGIS? , but I need to convert set or array of LON & LAT in PostgreSQL into geometry value. For example, I can convert ...
0
votes
0answers
30 views

Connect GeoServer to ESRI PostgreSQL database

I want to connect to an Enterprise geodatabase in PostgreSQL from GeoServer. The ESRI GDB is not running a SDE instance. Can I do a direct connection from GeoServer? What is the right way to put the ...
0
votes
1answer
50 views

PostgreSQL function return list of geometry

I have a query that return result as expected - list of points. I would like to create a function with two parameters: input polygon geometry N as number of points to create WITH polygon AS (...
4
votes
1answer
82 views

Inserting new features to PG\PostGIS table with generated column using QGIS?

I'm using PG 12.2 with PostGIS 3.0.0, and I've created a table with a generated column: CREATE TABLE public.test1 ( id integer NOT NULL DEFAULT nextval('test1_id_seq1'::regclass), geom ...
1
vote
1answer
39 views

How to remap a raster to its min / max values in PostGIS?

I have a table of 16-bit signed (!) integer heightmaps (SRTM tiles) loaded into PostGIS. This website showed me how to export a raster as a PNG to a file: COPY (SELECT encode(ST_AsPNG(raster), ‘hex’) ...
1
vote
1answer
48 views

QGIS Project variable and PostgreSQL connection

Is it possible to use project variable in the PostgreSQL database connection in QGIS? Usecase: Each user will set his/her db_user variable as part of the project. Once the project is shared among ...
4
votes
1answer
143 views

How to track progress on a route?

I want to track and visualize the progress of a bus, as follows: I have the coordinates of each station as well as the coordinates of the bus, updated every 10 seconds The information is stored in ...
29
votes
7answers
12k views

How can I update ogr2ogr to include the PostgreSQL driver?

I'm trying to use ogr2ogr to upload a shapefile to a remote PostGres installation. When I ran this command: celenius:~ celenius$ ogr2ogr -f PostgreSQL PG:"host=255.34.00.00 user=postgres dbname=mydb ...
0
votes
0answers
34 views

How to plot postgres data on a map?

I have a table which has latitude, longitude and some sensor data (data 1, data 2,data 3) saved in postgresql 12. I am running the database on ubuntu 18 virtual machine with windows as host. I dont ...
1
vote
3answers
174 views

Why the result of executing a geometric user function is different from the result of execution of the corresponding CTE query

So, I wrote a recursive query that works well, maybe only for me :-), [https://gis.stackexchange.com/a/333562/120129] WITH tbla AS (SELECT (ST_DumpPoints(geom)).geom FROM adm_polygons), ...
0
votes
0answers
56 views

Optimising Cross Join PostGIS Query

I have tried performing the query shown below via ogr2ogr and the gpd.GeoDataFrame.from_postgis() method in geopandas. from osgeo import ogr import psycopg2, os import geopandas as gpd import pandas ...
2
votes
1answer
60 views

create lines from points table

I have a point table I have sorted by mmsi (it is the identifier) and by datetime columns. mmsi numeric, x1 double precision, y1 double precision, datetime timestamp without time zone, geom ...
3
votes
2answers
236 views

Applying ST_ClusterDBSCAN repeatedly until cluster sizes small enough

I am looking for a way to spatially clusters thousands of datapoints (potentially millions) based on distance, such that each cluster contains less than 5000 points. This is a similar question to ...
3
votes
1answer
187 views

Upgrading PostGIS and PostgreSQL but running upgrade to PostgreSQL it errors?

I want to upgrade PostGIS(2.0.4 ---> 2.2.3) and PostgreSQL (9.2.4 ----> 9.6) so First I upgrade PostGIS 1、install PostGIS 2.2.3 on postgresql 9.2.4 2、alter EXTENSION postgis 2.0.4 ---&...
1
vote
0answers
29 views

Linking new feature violates foreign check constraint in PostgreSQL database when working in QGIS3

I have set up a spatial database in PostgreSQL in which I have some data/tables that need to be linked by M to N relations. For those occasions I have two tables that contains geometry and a pivot ...
0
votes
2answers
30 views

PostGIS Web service call function

I have spatial function in my Postgres/PostGIS DB that when sent a set of coordinates (Long Lat) returns single value. Whilst I'm not a web developer, i'd like to serve the function up as a web ...
0
votes
1answer
167 views

Spatial queries with PostgreSQL on AWS

I have some data in PostgreSQL which I am exporting to Amazon RDS service. Will I be able to use PostGIS extension in AWS, Also will I need the mandatory column "geom" or I could run queries with ...
1
vote
1answer
28 views

Mixed SRID Geometries

I'm developing a PostGIS function to return a value when longitude and latitude are passed as variables to the function. CREATE FUNCTION flood_location(long float, lat float) RETURNS varchar(40) AS ...
0
votes
1answer
277 views

Appending columns from a table to my active table?

I am trying to append three columns from srs2016 to srs2013_1 using this sql. INSERT INTO srs2013_1 (the_geom, agency_responsible, description) SELECT the_geom, agency_responsible, ...
4
votes
2answers
32 views

How do I retrieve a single image from an ImageMosaic in GeoServer?

If I have an ImageMosaic in GeoServer containing several raster images that overlap, is there a way to retrieve/display one of the original images? Could I use CQL or WCS to get them by fileName or ...
1
vote
1answer
95 views

ST_MakePoint outside PostGIS

To import data into a PostgreSQL/PostGIS database I need this CSV format: "1,nameOfPoint,0101000020E6100000689DA690152F114090338CFF9B0B4A40,2019-07-22 19:56:03.603+00,2019-07-22 19:56:04.243+00" ...
8
votes
1answer
6k views

How to deal with “ERROR: GEOSUnaryUnion: TopologyException: Self-intersection”?

I'm using Postgres 9.4 and PostGIS 2.1. My table looks like this: ------------------------------------------------------------------ ogc_fid | integer | not null default ...

1 2
3
4 5
67