Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
2,962
questions
1
vote
1answer
36 views
PostgreSQL create line from points
How do i create a linestring from points sort by date in PostgreSQL?
I tried:
SELECT observations.id,
ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom
FROM observations
...
1
vote
0answers
22 views
PostgreSQL points to line error [duplicate]
I have GPS locations with date. These are points.
I want to convert the points to lines, sorted by date.
Using PostgreSQL
Tried:
SELECT observations.id,
ST_MakeLine(observations.geom ORDER ...
1
vote
3answers
36 views
PostgreSQL - attach to point table the maxium distance value to edge of containing polygon
I'm trying to find the furthest distance from a point to the edge of the polygon it sits in. The polygons in this case have been converted to vertex points.
I have two POINT tables in Postgres (and a ...
-1
votes
0answers
20 views
PostgreSQL : double entry board [on hold]
I have a table of the type:
col1 col2 col3
N1 A C1
N2 B C1
N3 A C3`
How can we make a double entry array of the type under PostgreSQL ?
C1 C3
A
B
...
0
votes
0answers
30 views
PUT request not supported Error [on hold]
I having trouble configuring geoservice in openMaint
The problem is when I enable geoservice and fill the needed info I get the following error:
‘error checking gis schema: org.cmdbuild.gis....
2
votes
1answer
31 views
ST_MakeLine not returning proper latitude/longitude values
I have a table consisting of latitude, longitude and geom values:
index | longitude | latitude | geom | cluster_id
----...
1
vote
1answer
34 views
Copying data from one table to another automatically using PostgreSQL [closed]
I have a database with two schemas, "public" and "versioned".
In "versioned" I created a copy of the data contained in "public" that I update daily.
Is there an automatism for the data that I add ...
1
vote
1answer
18 views
pivot-table and group geometries
Postgresql11 - postgis2.4
Here is a table mytbl with points and an attribute var :
geom | var
pt a
pt b
pt1 a
pt1 a
pt1 b
pt2 c
...
I want to "rotate" the table like this :
...
0
votes
1answer
38 views
Tally of sequences of points within point-specific buffer, rolling over timestamps, in PostGIS?
In a given string of points, I've been able to designate buffers for each point in the string (i.e. a string of points are relocations over time). However, I'd like to be able to then summarize how ...
0
votes
1answer
49 views
QGIS can't connect to PostGIS but pgAdmin4 works
I have a PostGIS DB at a remote server and I can connect to it from my desktop using pgAdmin4. However, when I try to connect though QGIS (v3.4) I get an error:
FATAL: no pg_hba.conf entry for host "...
1
vote
0answers
24 views
Using layer style in PostgreSQL
I have a db with two schemes "public" and "versioned" (the latter generated with the fastversion plugin).
In the "public" scheme I have shp with related styles_table, in "versioned" I have other shp ...
2
votes
0answers
50 views
Cannot edit QGIS project in QField (save to PostgreSQL db)
I struggle to edit my project in QField on a Samsung Galaxy Tab Active 2. The general idea would be to create and edit points in the field and save them in a PostgreSQL db (offline and online).
What ...
1
vote
0answers
25 views
Move points to the main façade
How can I move the points to the main façade using Postgis?
I have used the following query
SELECT ST_ClosestPoint(ln.geom, pt.geom)
FROM "testes"."line1" AS ln
WHERE ((pt.geom <-> ln.geom)...
1
vote
0answers
18 views
n:m relations of lines including directions
For trail planning and route signalization projects, I am looking for a solutions which allows me to connect "physical" trail segments (available as .gdb from authorities) with overlaying routes, ...
2
votes
3answers
54 views
Use variables in PostgreSQL/PostGIS code
I'd like to create an SQL file, where the user can just change variables on the beginning , and use it in different configuration system. Typically for load layer from different PostGIS server with ...
0
votes
0answers
18 views
renderd: “rendering failed with command 4, pausing.” when rendering tiles but no error in syslog
I just had to rebuild my server and I'm having an issue when trying to pre-render my tiles. I'm getting a lot of "rendering failed with command 4, pausing." I am getting tiles (so renderd is running) ...
1
vote
1answer
26 views
Transfer spatial data between databases using pgAdmin4
I'm new to pgAdmin4 and performing tests before jumping in the deep end.
I've uploaded a large dataset into a the public schema within a database named 'spatial_db'. Rather than re-uploading the data ...
1
vote
1answer
36 views
Using Select statement inside of array constructor in PostgresSQL?
I'm working with point coordinates in PostGIS and I have a problem. My goal is to generate linestrings by using ST_MakeLine geometry constructor (see snippet 1). Source data is pre-generated ...
3
votes
1answer
54 views
Obtain best-fit bearing for sequence of points
I'd like to calculate the overall bearing (direction) of a sequence of points in PostGIS.
It's clear how to do this for two points, using ST_Azimuth. How can I do this for the best-fit line through a ...
2
votes
2answers
76 views
How to get distance between a point and a list of highways?
I'm new to GIS. I'm trying to determine whether a list of GPS points are within a highway/road (in terms of meters). I have downloaded a OSM file and populated my database using osm2pgsql.
I have the ...
2
votes
0answers
39 views
How to make Dijkstra faster?
I would like to compute shortest path between 2,5 millions pairs.
I created table with id, origin, the nearest node of origin, destination, the nearest node of destination.
I also created index on ...
1
vote
1answer
34 views
Speedup function to calculate distance from centroid of building to river segment in PostgreSQL
I am trying to measure the distance from the centroid of a building to a river segment using the following function.
CREATE OR REPLACE FUNCTION river_foo_mapper () RETURNS trigger LANGUAGE plpgsql AS ...
2
votes
0answers
33 views
Storing distance between two points in KM
I am very new to PostgreSQL and PostGIS. I am filtering a collection of messages based on their distance from a point which works great, but I would like to be able to store the distance between the ...
2
votes
1answer
299 views
Installing PostGIS on PostgreSQL 12
I installed PostgreSQL 12 using Windows Installer. After the installation, there´s no option to install postgis extension. I tried to add postgis support in a database using CREATING EXTENSION and I ...
1
vote
1answer
65 views
Ways to speed up ST_DWithin between millions of segments and thousands of polygons?
I have 3 PostgreSQL tables with the following data:
tab1 => It contains about 5.7 millions of segments (straight lines)
tab2 => It contains 6673 polygons
tab3 => It contains 19620 polygons
...
1
vote
0answers
35 views
Editing PostgreSQL/PostGIS table via join in QGIS
I have a standard PostgreSQL/PostGIS table and another table which stores a geometry. They both already have a relationship with ondelete/onupdate cascade.
I make a PostGIS connection in QGIS 2.18, I ...
0
votes
0answers
20 views
pgpointcloud repackaging point or x, y, z geometries to patch
I have a pointcloud in PostgreSQL/postgis imported via PDAL, everything is fine, I chipped it into groups of 600 points, no problems.
I then manipulated the data, first exploding the patches and ...
1
vote
0answers
14 views
Extracting relevant data from CAD format into a PostgreSQL database
I have received some survey information in the format of a .DWG (AutoCAD file) and I am trying to extract the data from this file into QGIS and then into a PostgreSQL database.
In the .DWG file there ...
1
vote
0answers
29 views
pgr_dijkstra invalid memory request size
I am trying to create a network of paths using pgr_dijkstra. I keep getting an invalid memory error:
ERROR: invalid memory alloc request size 1080000000
CONTEXT: SQL function "pgr_dijkstra" ...
1
vote
1answer
27 views
Export to DXF from Multipoint Z geometry in PostgreSQL using ogr2ogr
I want to export a PostgreSQL table of geometries in Multipoint Z to DXF using ogr2ogr
I tried this command
ogr2ogr -f "DXF" output.dxf PG:"host='127.0.0.1' port='5432' \
dbname='dbname' user='user'...
1
vote
1answer
42 views
Error while using ST_Line_Interpolate_Point: first argument isn't line
I've been running a scheduled task that contains some SQL code that has been running fine up until a few weeks ago. I don't believe any of the code had changed recently so I went through the debugger ...
1
vote
0answers
29 views
Osm2pgsql failed Cannot detect file format. Try using -r
I use Osm2pgsql 1.0 and this my action :
sudo osm2pgsql --slim -c --cache 29000 --proj 3857 russia-latest.osm.pbf -H xxxx.local -d osm -U osm_user -W -P 5432
was failed after some time:
...
2
votes
0answers
38 views
Issues synchronizing QField into PostGIS
I'm having issues synchronizing my QField project into a PostGIS database.
Nothing is synchronized, and I have the following error in the PostgreSQL logs:
2019-08-29 13:06:34 HST INSTRUCTION : ...
1
vote
0answers
22 views
Shortest path between points (from two layers) in pgRouting [duplicate]
I have three tables:
Place of residence
Entrances to the green areas
Pair of place of residence and green areas, which are in buffer - without geometry
I would like to find the shortest path for ...
1
vote
1answer
75 views
Dataframe of points to PostGIS
I have a csv file with some info and coordinates and I want to create a table in PostGIS with that CSV file. This is what I have so far:
import pandas as pd
import geopandas
from datetime import ...
2
votes
1answer
61 views
Connecting to PostgreSQL database from another machine using QGIS?
I have a database with name "Test" in PostgreSQL 11 in my machine, I can connect to this database from my machine using QGIS with host localhost, but when I tried to connect it from a different ...
2
votes
1answer
56 views
How to calculate line complexity index
I'm wondering if there is any suitable algorithm that can I use to calculate line complexity index (or indicator)? By "line complexity" I mean how many bends (direction changes) that line contains and ...
1
vote
1answer
35 views
PostgreSQL password setup in QGIS (disallowing multiple authentication to individual databases
I have a QGIS project where I am connected to about 10 databases. The issue I have is that I cannot seem to save the password to disallow repetitive password authentification. After logging in ...
3
votes
0answers
73 views
PostGIS JOIN table ON ST_Intersects
The following query and subquery help me finding a parcell using a GPS coordinate:
SELECT row_to_json(fc) FROM(
SELECT 'FeatureCollection' As type,
array_to_json(array_agg(f)) As features
...
2
votes
1answer
34 views
How to find max value for id appearing anywhere in another column?
I hope that title is clear. If not, please change.
I have table, which look like this:
I would like to create a select query, where if gid is in anywhere in ancestor column, select max level from ...
1
vote
0answers
17 views
Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error? [closed]
I'm trying to set up some kind of constraint so that I can only enter values in "Table A", if that value exists in "Table B".
I want this to act sort of like a selection list, where there are only a ...
3
votes
1answer
44 views
Clustering geometry points, return a point's label if the point does not belong to a cluster?
I am clustering geometry points using the k-mean algorithm. The result is a list of clusters. Some clusters contain multiple points. Others only contain a single point. On the client side, we would ...
0
votes
1answer
38 views
Leaflet: flyToBounds location of bounding box returned by PostGIS
I want to simply center my map on the search result of a polygon using leaflet. I thought to return the bounding box, as this is closer to what I actually need.
I want to use flyTo()
I see a ...
2
votes
1answer
39 views
ST_3DIntersection PostGIS incorrect result
I have tried multiple times to obtain the 3D Intersection between a 3D linestring and a solid cube in both PostGIS 2.5.2 and 2.5.3 with the SFCGAL backend.
Below is a query that works as expected and ...
1
vote
3answers
73 views
Selecting non-overlapping polygons from a one layer in PostGIS
I have one layer with overlapping polygons just like in this screenshot.
I would like delete all overlapping polygons. Something like that (yellow polygons).
I tried to use this code:
SELECT pol1.*
...
0
votes
1answer
40 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
0answers
30 views
ST_Intersects PostGIS returns empty table [closed]
Here is a link to download the two layers I am working with (as geopackages).
I have two tables in a PostgreSQL database, 'WataugaResidential' and 'zone1shape'. I'm trying to use ST_Intersects ...
1
vote
1answer
42 views
Why does QGIS treat NULL values in VARCHAR columns as real data?
We've noticed in QGIS (v. 3.8.1) that VARCHAR columns from PostgreSQL(v9.5) containing empty values are being read as the text value of the word "NULL".
Here's the data in QGIS (highlighted records ...
1
vote
1answer
45 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"
...
2
votes
1answer
29 views
Making a raster coverage that shows the average speed of points
I had a layer of points of trajectory and I wanted to create a map of the average speed of the movement. I have a column of speed in the table of points.
I created a big base raster coverage. Then, I ...
