Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
2,921
questions
0
votes
0answers
7 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 ...
0
votes
0answers
23 views
ST_Intersects and PostgreSQL geometry problems
I updated PostgreSQL to version 11.5 and some strange things are happening. I added some layers from QGIS into a PostgreSQL database, with PostGIS enabled.
Here is what one layer looks like in QGIS. ...
1
vote
1answer
66 views
Query in QGIS is significantly slower, than in pgAdmin
So, I've a table in postgresql, that is joined to other spatial table.
If I've run a simple query in pgAdmin, let's say SELECT * FROM table1 WHERE "area" > '5000' and it takes less, than 1s to ...
2
votes
0answers
19 views
Querying / seting up PostGIS (raster) table to efficiently get all bands at once?
I have created a PostGIS raster table from raster2psql. I have use cases where I might have bands sizes of 114 to 670 bands inside a raster (The number is consistent across a given dataset.)
My ...
1
vote
1answer
44 views
PostGIS - not a valid Win32 application error
I'm trying to install PostGIS but with no luck. I've installed PostgreSQL 11 x64 and PostGIS Bundle 2.5.2 x64 and when trying to create an extension in pgAdmin I get:
ERROR: could not load library "...
0
votes
1answer
57 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 ...
1
vote
1answer
18 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 [closed]
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
21 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
44 views
Error in installing PostGIS extension through PostgreSQL 11
I am getting following issues while installing PostGIS extension.
0
votes
0answers
39 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
32 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
25 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 ...
7
votes
2answers
1k 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
26 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
24 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
25 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
37 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
32 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
35 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
71 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 ...
3
votes
0answers
49 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....
3
votes
1answer
36 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
30 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
13 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
33 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
2answers
58 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
95 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
51 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
36 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
78 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
102 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
44 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
78 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
39 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>...
