Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

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

QGIS Project saved in PostgreSQL not loading on other computers

I have created QGIS projects that are reading from PostgreSQL(data source). I then saved the individual projects to PostgreSQL in the public schema under their respective databases. When I open or ...
0
votes
0answers
7 views

Find every possible user to user travel time duration using pgr_dijkstra or pgr_kDijkstra

I have this below GetNearest function that will get every user's nearest node from ways (imported using osm2pgrouting). CREATE or REPLACE FUNCTION GetNearest(NUMERIC, NUMERIC) RETURNS BIGINT AS ...
0
votes
0answers
14 views

PostgreSQL self-join query condition not to return repeated results [on hold]

I have this simplified query : SELECT a.p_key, b.p_key FROM test AS a, test AS b WHERE a.p_key != b.p_key -- skip self AND a.p_key < b.p_key -- symmetry breaker -- missing extra condition ...
1
vote
0answers
13 views

Speed up the shortest distance calculation using pgRouting + PostGIS

I have to calculate the shortest path and corresponding distances for 300k+ origin-destination (OD) pairs. Using the query below it takes me around 0.3 sec to calculate one distance and therefore more ...
0
votes
0answers
39 views

Error in installing PostGIS extension through PostgreSQL 11

I am getting following issues while installing PostGIS extension.
0
votes
0answers
33 views

PostGIS DLLs could not be loaded but they are there

I've got DLL loading error when trying to restore dump files. I'm on PostgreSQL 11.5 (64bit, of course) and last time I checked all DLLs it claimed are exactly there already. ERROR: could not load ...
0
votes
0answers
17 views

Getting node way relationship from Osm2pgsql data

I would like to ask what is the best way to extract which way a node belongs to given a Postgresql databased generated by Osm2pgsql. For example, in https://www.openstreetmap.org/node/4961890042, ...
0
votes
0answers
29 views

PostgreSQL - can't add PostGIS extension to database

Long story short I am trying to enable the PostGIS extension in PostgreSQL databases. I am having some trouble doing this and don't really know why. I updated PostgreSQL to the newest version, which ...
2
votes
1answer
23 views

'Must have equal len keys and value when setting with an iterable' error message

I have a postgres spatial table that I want to convert to a geopandas GeoDataFrame. In order to do so I wrote the following piece of code: #'pg_tab_clip' is the name of the postgres spatial table #'b'...
1
vote
1answer
20 views

Unioning a set of intersections [closed]

I'm working with two datasets: a set of precincts and a set of census block groups. The census block groups include water; the precincts don't. I would like to trim the census block groups to get only ...
0
votes
0answers
30 views

Broken PostgreSQL layers?

I'm adding some layers from a PostgreSQL/PostGIS database. They worked fine yesterday, but today nothing is working and I really have no idea why. Here is my process to connect to the database: Now ...
0
votes
0answers
21 views

Can't add layers to new PostgreSQL database

I made a new PostgreSQL database and am trying to add layers/tables to it in QGIS using the DBManager. And I am failing. Here is the error message I'm getting: However, I can add layers to other ...
5
votes
2answers
546 views

ERROR: could not load library “C:/Program Files/PostgreSQL/11/lib/rtpostgis-2.5.dll”: unknown error 126?

I have got this message when I wanted to create a new extension. How can I solve this problem? I have Win10 64 bit and PostgreSQL 11.
0
votes
0answers
25 views

Find a point in a polygon shape

I have a table that contains a column polygon shape. I need a query that I Want to pass a point(coordinates) if point contains in a polygon_shape of an instance the match will be returns . select * ...
1
vote
0answers
21 views

Shp2pgsql-gui.exe - system error

I have got this message when I was trying to run PostGIS 2.0 shapefile and dbf loader and exporter for first time. I tryed to reinstall but it did not run after second installation either. I have ...
2
votes
0answers
21 views

Restoring a PostgreSQL/PostGIS backup into a new database with fresh extensions

I'm working on a migration from a very old server (Ubuntu 10.04/Postgres 8.x, etc) to a new instance (18.04) with up-to-date versions of everything in our web stack. Our PostgreSQL database uses a ...
1
vote
1answer
36 views

Find a point in a polygon

How can I find all points in a polygon pass in queries: My column coord is a geometry point example, pseudo-code. select a.* from "table" as a where ST_WITHIN(a.coord, [lng1 lat1, lng2 lat2, lng3 ...
1
vote
1answer
30 views

Unloading addresses from database to Nominatim/Pelias geocoder?

I don't works with geocoding before. But I have geocoding task now. We have postgreslq database with addresses data. And we have two ways for geocoding: nominatim and pelias (are placed on different ...
2
votes
2answers
30 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 ...
2
votes
2answers
51 views

Perform query on multiple tables

I need to apply the following query to 60+ tables: UPDATE variable_table_name ab SET elem_nr = ef.elem_nr FROM ga_mit_elemnr ef WHERE (ab.elem_nr NOT IN ( SELECT ...
2
votes
2answers
69 views

PostGIS `COPY` geometry with SRID?

Is it possible to use COPY on a file full of WKT POINTs without dropping the explicit SRID on the destination column? I'm trying to insert several million rows of WGS84 points and am using COPY for ...
2
votes
0answers
43 views

Changed attribute order of PostGIS layer in QGIS

I'm working in a PostgreSQL table (PostGIS) that I loaded into QGIS. This layer has been created by a SQL Statement in pgAdmin. I noticed that the order of attributes shown in the attribute table ...
1
vote
1answer
50 views

Failing to JOIN ON ST_Contains

I have two tables of polygons: Table_1and Table_2. All the polygons of T1 are inside the polygons of T2. I want to SELECT the id of T1 and the name of the polygon from T2 where the polygon is located....
2
votes
1answer
28 views

Creating a raster with point count as cell value?

I have a PostGIS table with Point geometries in it. I want to create a raster object where each cell value is the number of points within a given raster cell. Cells with zero points in it can be 0, ...
0
votes
0answers
29 views

Connect to Enterprise GDB in PostgreSQL using QGIS : “No Geometry” Error

I have an esri Enterprise Geodatabase (9.3) stored in MSSql 2008 with storage type as sde binary. I upgraded it to EGDB 10.4.1 (using multiple up-gradation steps) in MSSql 2012. Then I copy/paste my ...
0
votes
1answer
12 views

Using features of CARTO Builder to dynamically calculate the average of selected columns

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-...
1
vote
1answer
29 views

Count points from two tables in polygons with PostgreSQL/PostGIS

I've got CREATE TABLE points1 ( point1id varchar PK, geom1 geom, date1 date); CREATE TABLE points2 ( point2id varchar PK, geom2 geom, date2 date); Both with indexes. I ...
1
vote
1answer
21 views

Restore backup from pgadmin 3 to 4 [closed]

Is there an easy way to restore databases from PGadmin 3 into Pgadmin 4? Right now data on both, and I want to gather all my data on the same server. The port on PGadmin 3 is 5432 and 5433 on PGadmin ...
0
votes
1answer
23 views

Connection QGIS 3.4.5 / PostgreSQL 9.3 with role group [closed]

I created 2 connection roles with PostgreSQL. Then, to manage more easily the privileges, I created a group role includind these roles (inherit). I created a trigger which track data changes. I used ...
0
votes
1answer
32 views

Getting the width or maximum distance of multilinestrings [closed]

I have several multiline strings. Each string consists of 2 to 8 almost parallel lines. I now want to have the largest distance between the two outer lines or calculate the maximum width of my ...
1
vote
1answer
47 views

Problems with ST_ClusterDBSCAN cluster sizes

I want to cluster all villages (and towns, ...) around the world. So instead of having millions of them, I'd like to reduce them by combining villages that are close enough (like 10km or so) to each ...
4
votes
2answers
93 views

Determine If PostGIS Geometry Is a Circle

I have a PostGIS database table that contains several generic geometries in one of it's columns. Sometimes I have geometries that are intended to be circles which I insert into the database as the ...
1
vote
1answer
47 views

How to get all the sub-rectangles (subgeohashes) of a given geohash

I am trying to use geohash to generate a grid of rectangles to cover an area. But I am not very familiar with the coding system of geohash. My question is: If I have a specific geohash gh of 3 ...
1
vote
1answer
34 views

Accelerate neighbor analysis in QGIS PostgreSQL / PostGIS

For a project I have to show the relationship between 2 or more objects. For this I use the code below in QGIS in step 1 I determine the relation in step 2 the items are counted. In the test set I use ...
0
votes
0answers
35 views

PostGIS Out-DB Rasters - Segmentation Fault

I am loading data from a NETCDF file with raster2pgsql, which I would like to keep as an out-DB raster. The command I use is (note the -R option): raster2pgsql -s 96482 -I -d -F -R NETCDF:"/home/...
1
vote
1answer
25 views

Parse Error on PostgreSQL when using ST_AsText

I am new to using POSTGIS and PostgreSQL. I am trying to convert PostGIS coordinates into lat and long values. When I copy one of the values and query like SELECT ST_AsText('...
2
votes
1answer
76 views

Show points (layer) from PostgreSQL database on OpenLayers map

I have this code, I want to load a collection of points stored in a table in PostgreSQL database, I retreive the GeoJSON format succesfully, but the points never show up on the map! The three files ...
1
vote
1answer
28 views

Insert empty VIEWS into QGIS from PostgreSQL using PyQGIS

The Aim: I am trying to insert a number of empty VIEWS into QGIS using PyQGIS. These VIEWS have been created and stored in a PostgreSQL database. I want to also ensure i can successfully re-open ...
3
votes
0answers
101 views

Computing driving directions in pgRouting/PostGIS?

I have calculated shortest route/path by using pg_Dijkstra algorithm in PostGIS and it is working fine. Now how to calculate driving directions from starting edge to end edge. I have gone through the ...
1
vote
1answer
53 views

PostGIS- convert points to polygon based on multiple shared attributes

I have a PostgreSQL PostGIS point table (not multi-points). I want to convert the points to polygons (using convex-hull?) grouping the points by shared attributes from 2 columns. In this example, '...
2
votes
1answer
39 views

Unable to load geometry field from geodataframe to a PostgreSQL table 'geometry' field

I am trying to load a geodataframe to a PostgreSQL table, but I am struggling with the geometry field. First I read feature class using Geopandas: geodata = (r'path_to_gdb', driver='FileGDB', layer='...
2
votes
2answers
107 views

How to load the geometry field of a feature class to PostgreSQL?

I am trying to load a polygon feature class in a file geodatabse to a PostgreSQL table (extension PostGIS installed). This the process I'm following: I create table in PostgreSQL with required fields ...
1
vote
0answers
77 views

PostGIS: proper way to model object that follows sections of another object's path?

First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words. I'm trying to model aerial utilities in a PostGIS ...
0
votes
0answers
38 views

Transfer indexed Oracle geoloc table to PostgreSQL using ogr2ogr

I am copying tables using ogr2ogr from Oracle to PostGIS using ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME&...
1
vote
0answers
19 views

Oracle to PostgreSQL migration with ogr2ogr only imports first geoloc column

I transfer Oracle tables to PostGIS using Oracle Wallet and ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME>...
1
vote
0answers
34 views

Combine data of OSM roads into longer lines using PostGIS

For my vector map I'd love to display highways at a quite early zoom level. The problem is that this data would be quite big, so I thought I simply ST_Simplify it. The problem though is that the OSM ...
3
votes
1answer
36 views

How to set schema parameter in osm2pgsql calling

How I can to set schema name parameter when call osm2pgsql? For example: osm2pgsql --create -s -C 2500 -G -S D:\default.style -d osm_db -o input -U osm_user -W -H localhost -r pbf D:\osm.pbf I set ...
1
vote
0answers
30 views

Whole schema migration from Oracle Spatial to PostGIS import using ogr2ogr

I can migrate Oracle SQL table to PostgreSQL using ogr2ogr using: ogr2ogr -progress -f "PostgreSQL" -nln schema.table -skipfailures -overwrite -lco FID=ID_COLUMN -lco GEOMETRY_NAME=geom PG:"host=...
2
votes
1answer
154 views

Automatically add points geometry while GPS logging?

I have a PostgreSQL 10 Server (x64) running on my Windows 8 (x64 based) machine. On the top of my Postgres database, there is PostgREST API running that receives GPS data (5 seconds interval) from a ...
1
vote
1answer
92 views

PostgreSQL: Copy the line segment together in another table without copying the intersection with another line segment

Presently these linesegments are in different rows but in the same table and numbers shown in the image are the ids of respective linestrings. I want to copy linesegment 350, 333, 352,4,.. in one ...