Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

1
vote
1answer
711 views

Calculate total line lengths within a 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
2answers
178 views

Why the indexes for the attributes are not being used when GeoServer asks PostGIS?

I am working on a WMS to publish data with GeoServer (2.11.0). Tables come from PostGIS (PostgreSQL 9.6). I have indexed geometries on the tables and i have also indexed attributes which are being ...
6
votes
1answer
383 views

Display pictures stored as bytea in postgresql column in qgis

does someone knows a possibility to visualize in a bytea column saved photos of a postgresql database in qgis? I would like to store pictures directly into the database, not just the link/ url.
1
vote
1answer
40 views

How to split vector layer in PostgreSQL/PostGIS

QGIS has this nice tool to Split a layer into multiple layers based on one attribute and create a new layer for each unique value within that attribute. Is this also possible using PostgreSQL/PostGIS?...
0
votes
1answer
27 views

Text File MultiPolygon/Polygon to PostgreSQL using Python/Psycopg2

I am writing a python script attempting to insert GeoJSON Polygon coordinates into a Postgres table. I am writing a basic insertion code since I am new to GIS domain. The code look like this: ...
3
votes
2answers
206 views

Create triangle fishnet

I am looking to create a fishnet of triangles with a specific x,y start location and angle. I essentially want my fishnet to follow the boundaries of a hexagon grid that I have. I found the Jennes ...
5
votes
4answers
2k views

Debugging ogr2ogr “AddGeometryColumn failed” and “Terminating translation prematurely after failed translation of layer”

As noted in the title, I'm getting the following error: ERROR 1: AddGeometryColumn failed for layer pretty_polys, layer creation has failed. ERROR 1: Terminating translation prematurely after failed ...
3
votes
1answer
126 views

Accessing PostGIS data in QGIS?

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS ...
1
vote
1answer
27 views

How to aggregate other columns when ST_ClusterIntersecting function is used

As stated in the PostGIS documentation ST_ClusterIntersecting is an aggregate function that returns an array of GeometryCollections, where each GeometryCollection represents an interconnected ...
8
votes
2answers
8k views

Importing large dataset with osm2psql? [closed]

My question is similar to slow import via osm2pgsql to postgresql database and Optimizing osm2pgsql imports for OSM data but as we currently have quite some problems importing a large set of OSM data ...
1
vote
1answer
112 views

From QGIS 3.4.5 and 3.6 asks password repeadly to connect to PostgreSQL 10 [closed]

Action: At first time when opening project on QGIS 3.4.5 and 3.6 versions needs credentials to connect to PostgreSQL DB. Problem: But after that when connection is success with correct credentials ...
1
vote
0answers
38 views

How to store geometry data polygons (list of coordinates in tuples) in PostgreSQL? [closed]

Whenever I try to import the geometry column it contains points in list i.e, [(lat1,lon1),(lat2,lon2),.., so on] this points in list makes a polygon and polygon requires list of tuples, (dataframe to ...
0
votes
1answer
142 views

Uploading shapefile to PostGIS database using PHP

I want to insert a shapefile into a PostGIS database using PHP. I go through the examples and I tried the following: $command = 'shp2pgsql -s 2100 -d /D:\CHECK DATA-group\2016\Ayomi\JULY\...
0
votes
2answers
124 views

PostGIS: Change raster values depending on values from other raster

I have two rasters and I want to change all the values from "raster1" to 4, where they are larger than the values from "raster2" and to 0, where they are smaller. So raster2 has only one value, which ...
1
vote
1answer
27 views

ST_ValueCount() returning multiple rows of same VALUE

I want to calculate the proportion of each pixel value in a raster after clipping a much larger raster to my area of interest. However, after clipping and saving the raster from PostgreSQL database/...
0
votes
0answers
16 views

Getting active PostgreSQL connection username when stored through authentication configuration in QGIS 3

I've been trying to get the username of a PostgreSQL connection in QGIS 3.6, using this Python code layer = iface.activeLayer() provider = layer.dataProvider() username = QgsDataSourceUri(provider....
2
votes
1answer
77 views

Issue with GeoJson data format fetching from PostgresQL

I am trying to display the GIS data on the Google map using PostgreSQL. I have few Shapefiles, I imported those shapefile into PostgreSQL. Now I am trying to get this data using this query. After ...
2
votes
1answer
499 views

Cache lookup failed for function 1 while enabling postgis on postgres 10

I am trying to enable PostGIS on a PostgreSQL 10 database on Windows. Earlier it used to work fine, but now I am getting an error 'cache lookup failed for function 1' on enabling the extension. I am ...
3
votes
2answers
264 views

difference between st_accum and st_collect

Can anyone please tell me the difference between ST_Accum and ST_Collect of PostGIS. https://postgis.net/docs/ST_Accum.html https://postgis.net/docs/ST_Collect.html
0
votes
1answer
28 views

Django demands to set default. ValueError: String input unrecognized as WKT EWKT, and HEXEWKB

when I try to makemigrate models with Geometry fields to postgres DB I face with next message: You are trying to change the nullable field 'geom' on allotment to non-nullable without a default; we ...
2
votes
0answers
43 views

Per row commit when editing PostGIS data with QGIS, with PostGIS triggers checking inserted data

I have a PostGIS trigger assuring that records with a Point-geometry inserted in a table fall within Polygons in another table. Simple example: -- Trigger function CREATE OR REPLACE FUNCTION ...
0
votes
2answers
189 views

Odbc_fdw.control is missing

I am using cartoDB and Postgres 9.3 including PostGIS. When I try to create development user in cartoDB I get this error: 'CREATE EXTENSION IF NOT EXISTS odbc_fdw SCHEMA public;' ERROR: could not ...
1
vote
0answers
28 views

Error with generate_series

I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093) DO $$ DECLARE _curs CURSOR FOR SELECT geom3857 ...
0
votes
0answers
30 views

Feature templates do not change when using LYR file and PostgreSQL?

We just started migrating from SQL Server to a PostgreSQL Server for production. Traditionally, we load a LYR file containing all of our symbology, then point it to our new GDB on the SQL Server. If ...
0
votes
1answer
41 views

How can I find a postal_code for a given Lat/Lon in a OSM database?

I have a OSM database on postgres/postGIS. Data are downloaded from https://download.geofabrik.de/ and created in postgres with osm2pgsql using schema osm2pgsql. Now I'm looking for a postalcode on a ...
1
vote
0answers
34 views

Creating closest line along the point''s azimuth using PostgreSQL

I have a point table which contains start point and end point: When I am writing this query in PostgreSQL to create line it gives me this output. select distinct on (a.id) a.id, min(...
1
vote
1answer
71 views

How to append OS MasterMap Feature Class to the Feature class in PostgreSQL database

I have my Enterprise Geodatabase as PostgreSQL. I want to keep OS MasterMap data on the Geodatabase and would like to update it whenever a new tile is downloaded. However, I am struggling to find a ...
0
votes
0answers
32 views

Perform Multiple Point in Polygon Queries PostGIS

I was wondering if there was a way to send one request to a PostgreSQL server with the PostGIS extension that contained multiple points, and then find the polygons that they are located in. For ...
2
votes
1answer
40 views

Copy the layer_styles table from one database to another?

I am trying to copy the layer_styles table to another database. A quick and painless process is preferred, to make the movement of the layer_styles tables easy to repeat in the future. I have tried ...
1
vote
0answers
29 views

Versioning in PostGIS on PostgreSQL database [duplicate]

I have a cadastral database which requires multi-user editing/updating on daily basis. I am trying to migrate from Esri enterprise geodatabase to PostGIS/PostgreSQL. I am not sure PostGIS offers ...
0
votes
1answer
272 views

Estimating direction of travel of road line segment based on long/lat in PostGIS?

Well, this seems pretty simple but haven't been answered cleanly in previous relevant questions for example: Identify the direction of traveling road segment and Finding compass direction between two ...
0
votes
1answer
1k views

How to resolve postgis.control location error on centos 7

Yum installed postgrepsql 10 and postgis 2.0 on a centos 7 host. Got this error while creating the postgis extension on a DB: could not open extension control file "/usr/pgsql-10/share/extension/...
1
vote
0answers
21 views

Data type for mixed representation

The following is a JSON representation of an large array of planar spatial surfaces. {"type":"Feature", "geometry":{ "type":"MultiPolygon", "coordinates": [[[[17.7529,-31.146],[17....
10
votes
0answers
656 views

Simplify the straight sections of (multi)Linestring in PostgreSQL/PostGIS leaving the curved sections untouched

I have (multi)linestrings containing upwards of 5000 points and I want to perform two simplifications possibly using Ramer Douglas Peucker or alternative methods in PostGIS. The (multi)linestrings ...
10
votes
1answer
831 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
99 views

Creating Polygons from Points in Carto

Using Carto, I am trying to create shapefiles of regions using points(Link to Dataset in Carto ) with a shared column value to group by. I've included 3 photographs. 1st photograph of the points ...
0
votes
1answer
72 views

Optimising a PostGIS table

I've been uploading a huge amount of contour information to a PostGIS / PostGres table. Uploaded using QGIS' db manager to a separate table a chunk at a time, and then using a quick bit of SQL to ...
1
vote
0answers
28 views

How to join multiple nearest neighbouring nodes using postgresql or python?

I have many short paths SHP file which look like a longer dashed path. Is there an efficient way to connect these paths to one continuous path? If possible, I want to avoid a hundred times manually ...
0
votes
0answers
25 views

QGIS Styles: How do I save rendering preferences in a style for future automation?

The following is in regard to a point layer in QGIS 3. Under Layer properties > Rendering > 'Refresh layer at interval (seconds)' I have set the interval to refresh every one second. The point ...
1
vote
0answers
42 views

PostGIS: Is it interesting to create an index on geom column and an alpha numeric field?

I would like to know if it's interesting to create an index on an other column than the geom one. In some cases, I would like to do spatial analysis. If not, I would like to analyse only attributes. ...
1
vote
2answers
80 views

ST_Union in PostgreSQL and QGIS

I have a problem with understanding of the ST_Union (PostgreSQL) and the union function in QGIS. Basically, I have as an example two layers A and B that overlap. The result when using union in QGIS is ...
4
votes
1answer
97 views

How to get the geometry value from all spatial tables in postgresql database

I'm trying to query the_geom value for all spatial tables in our DB. For one, I know I can get the tables in question with: select * from geometry_columns where f_table_name like 'ParcelPoints%'; ...
2
votes
1answer
77 views

QGIS and remote PostgreSQL/PostGIS slow performance

I'm using a cloud service for hosting my PostgreSQL/PostGIS database. When connecting with QGIS, its extremely slow. The observed behavior is: Connecting to PostGIS DB: works great. Adding a layer ...
0
votes
1answer
54 views

Query execution time

I am new to SQL. I am trying to execute some queries on a database using PostgreSQL with the PostGIS extension. I cannot understand why the execution of the following query takes about 5 mins select ...
1
vote
0answers
37 views

ERROR: function st_orientedenvelope(unknown) does not exist

I tried the example for ST_OrientedEnvelope given in PostGIS website but that failed. This function is failing for my table geometry column also. SELECT ST_AsText(ST_OrientedEnvelope('MULTIPOINT ((0 ...
1
vote
1answer
245 views

Enterprise geodatabase on PostgreSQL backup and restore

windows 7, PostgreSQL 9.4/ArcSDE/PostGIS backend database that I am transferring to another computer. My backup command is as follows. host 5433 pg_dumpall -h localhost -p 5433 -U postgres -c -f "...
0
votes
1answer
65 views

Running Query of pgRouting and PostGIS - dijkstra_sp_delta()?

Using PostgreSQL 10.4, compiled by Visual C++ build 1800, 64-bit PostGIS 2.4.4 pgRouting 2.6.0 and This Tutorial I am trying to run this Query $conn = pg_connect($conString); $sql = "...
13
votes
3answers
7k views

How to get the centroid of a set of points in PostGIS?

I use PostgreSQL with the PostGIS extension. I have a set of points in the_geom column from a table myschema.myobjects. I want to create a select statement to get the centroid of this cluster, so ...
9
votes
3answers
1k views

Uploading Geopackage contents to PostgreSQL

As a bit of background, we are looking to transition away from the use of shapefiles. We've already started using Geopackages for desktop work (QGIS / ArcGIS) but often have a requirement to upload ...
0
votes
0answers
28 views

Splitting lines by points in PostGIS bug

I am trying to split a network of line geometry by points in to non-overlapping subsets with PostGIS in PostgreSQL. I have tried multiple methods from stack: here and here. Every method seems to work ...