Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
0answers
43 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
0answers
18 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
17 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 ...
3
votes
1answer
172 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 ---&...
2
votes
0answers
43 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
90 views

Incorrect extent when importing shapefile to PostGIS

Using PostgreSQL 9.6, pgAdmin 4 v3.5 and QGIS I’ve found quite a bit of info on this issue but no clear answer. I’m attempting to import a shapefile that was created using ArcGIS (don’t know what ...
0
votes
2answers
130 views

Calculate minimum distance between feature types in PostGIS?

I have polygon layer in QGIS, and for each polygon I would like to calculate the minimum distance to the nearest polygon in another layer. My desired result is an attribute for all features that has ...
0
votes
1answer
198 views

Lizmap editor won't save edits to pgsql

So I have successfully installed Lizmap, Qgis-server, Apache, and a BigSQL postgreSQL database and published a map with a working WFS pointed at a PostGIS enabled table in my database. When I try to ...
2
votes
2answers
4k views

Add minutes contained in a column to a date

In CartoDB I want to add minutes contained in a column of data type number to a date. e.g. SELECT my_initial_date + 'xx minute'::INTERVAL as my_date FROM table What is working is: SELECT ...
-2
votes
0answers
14 views

PostgreSQL command to get the agg_cost value [closed]

Can someone guide me on how can I pass the agg_cost value to a variable from PostgreSQL?
0
votes
1answer
249 views

PostgreSQL Query which returns geom column as WKT set in function

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[...
1
vote
1answer
116 views

'UPDATE 0' results for ST_Within query - locating points within polygons

tldr; I cannot get ST_Within to find business points that fall within two sets of polygons. I keep getting zero results. All three tables have geom col with SRID 4326. I have a data table where I ...
2
votes
1answer
49 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
32 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 ...
0
votes
0answers
24 views

Line Grid from points in GIS (ArcGIS and QGIS) [closed]

I have a map with points which contains attributes such as lat, long and other information. What I want to do is join points depending upon the lat, long with reference to its nearest point in the map ...
3
votes
0answers
65 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 ...
3
votes
1answer
567 views

Querying contour lines for elevation at point using PostGIS?

Is there a way to interpolate between these contour lines for a given point using postgis? I've come up with the following query to gather the potential contour lines of interest: $sql = "...
4
votes
1answer
59 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 ...
5
votes
5answers
3k views

“libintl-9.dll is missing” when trying to run PostGIS 2.0 Shapefile and DBF Loader Exporter

When I try to run PostGIS 2.0 Shapefile and DBF Loader Exporter I get the following error: The program can't start because libintl-9.dll is missing from your computer. Try reinstalling the ...
3
votes
1answer
882 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
86 views

PostGIS and connection to QGIS issue?

I have a problem with PostGIS and a connection to QGIS. I successfully connected to a database on a server but when I try to add a PostGIS Table, I am getting warning symbols next to all my tables. I ...
3
votes
1answer
293 views

Mapnik + PostgreSQL + Python = Segmentation fault

I would like to render subset of OpenStreetMap features over big area into one image, no sub-tiles. System: Devuan ascii 64bit (alike Debian 9), 64GB RAM, headless (SSH only) There is what I already ...
1
vote
3answers
65 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.* ...
2
votes
1answer
32 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 ...
2
votes
1answer
236 views

postgis add vector tables together to create one weighted table

There are 7 polygon tables in postgis. Each table has a unique column that holds a weight of 1. so each row has a 1 in it. Without converting to raster I want to "add" up all the vector tables into ...
11
votes
1answer
892 views

Merge adjacent polygons and return all (adjacent and not adjacent) as Multipolygon?

I have a postgres database with administrative boundaries and their geometry. Each of those boundaries have an ident number. What I want to achieve: First I want to select all rows which are ...
0
votes
1answer
128 views

Problem to convert geometry to coordinates of geometry(MultiLineString) data

I am stuck converting a geometry(MultiLineString) into coordinates. The following I did. I download the data from this Austin Contours data link I import the shape files using PGAdmin III and ...
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
2answers
199 views

Maintaining Order and Repetition of Cell Names using PostGIS?

I am trying to get cells names (in a sequence) by way trajectory is passing through them. For example in the photo shown below: Blue markers represent a grid and red line represent a trajectory ...
3
votes
1answer
126 views

Getting the largest rectangle in the R-tree index of a PostGIS table?

Is it possible to use SQL or PL/pgsql to get the largest rectangle in the R-tree index (or other types of indices) of a table, so that it can be used as Bounding box for PostGIS table? If possible, ...
2
votes
1answer
119 views

ST_AsMVT single layer with multiple Id's

I'm trying to create mvt using postgis by following this tutorial If understand correctly ST_AsMVT is used to create a layer and generated layer will have id. same id can be used to apply styles. ...
1
vote
2answers
200 views

psycopg2.ProgrammingError: function does not exist

I am using Python code cur.callproc('function_a',[parameter1, parameter2]) for executing one function and this code is correct when I use a user having full DB permissions. But when I use a private ...
1
vote
1answer
54 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
39 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
31 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 ...
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
3answers
717 views

Storing shapefile in PostgreSQL database as a table using PHP?

I want to create a table from a shapefile in PostgreSQL. To do that, I can run this command in cmd: shp2pgsql -s 4326 -W LATIN1 E:\Any\THA_adm public.test123 | psql -h localhost -d DATA -U postgres -...
2
votes
1answer
37 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
1answer
39 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 ...
0
votes
1answer
201 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, ...
0
votes
1answer
35 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
28 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 ...
0
votes
1answer
38 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
20 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
55 views
1
vote
1answer
87 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
23 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
22 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 ...
1
vote
2answers
65 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 ...
1
vote
1answer
65 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 "...