Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
0answers
12 views

XSD to PostgreSQL

I want to create from CitygmGML ADE XSD file a PostgreSQL database schema. I have used Altova XMLSpy but the results were not satisfying. Is there a method to do that or a database that is created ...
0
votes
0answers
19 views

geometry point and varchar multicolumn index in PostgreSQL

Is it possible to create a multicolumn index on a geometry point column and a varchar column? For example, say I have a geometry point column called "location_point" and a varchar column called "zip". ...
1
vote
0answers
29 views

Access PostGIS on Heroku instance from GeoServer

In Heroku, I have a PostgreSQL DB with all the credential to be accessed by outside (Host, Port, Database name, User, Password, URI). Then I connected pgAdmin with this database using the above ...
0
votes
1answer
42 views

How can you convert between geographic data from and to 3D data in PostGIS?

Is there was a way to convert a geography value to a 3D geometry value (in ECEF, for example), and vice versa? I know how to convert between geography using a 2D projection, but not a 3D reference ...
1
vote
1answer
35 views

Speeding up PostgreSQL UPDATE query?

I have a query running as below: UPDATE datasets.riverside_houses SET easting = addressbase.x_coordinate FROM datasets.addressbase WHERE addressbase.building_number = riverside_houses.house_number OR ...
1
vote
1answer
42 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 ...
2
votes
3answers
43 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 [closed]

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 [closed]

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
39 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
54 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
54 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
19 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
55 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
20 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
29 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
55 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
358 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
66 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
22 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
28 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
43 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
31 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
40 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
77 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
62 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
58 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
46 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
41 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
40 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
74 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.* ...