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.

2
votes
0answers
14 views

Get the probable right location from multiple points

I have a location collection tool which send me location data of our survey. Surveyors send multiple data of a single item. Such as they send numarous data for a school like the following image. From ...
0
votes
1answer
17 views

How to change PostgreSQL layer encoding for QGIS Server

I'm actually stuck trying to update encoding of my PostgreSQL layers to be able to set my project as QGIS server project. In the layers properties, I can't change the layer's encoding. Any hints on ...
1
vote
1answer
41 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?...
1
vote
1answer
28 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 ...
0
votes
0answers
43 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
0answers
18 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....
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
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
35 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(...
0
votes
0answers
33 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
41 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
31 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 ...
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....
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
0answers
26 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
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 ...
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 ...
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
43 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. ...
0
votes
0answers
29 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 ...
1
vote
0answers
12 views

Editing attributes in ArcMap causes conversion of arcs to densified lines unintentionally

I am using postgresql GDB for storing and managing polyline feature classes through arcmap but when I create arc feature along with filling any attributes the arc is being converted to a densified ...
1
vote
2answers
34 views

Error when using UPDATE to populate a geometry column

I'm trying to update a column with new geometry but am getting ERROR: column "current_geom" is of type geometry but expression is of type boolean. UPDATE this_table t1 SET "current_geom" = ...
1
vote
1answer
51 views

ArcGIS not connecting to PostgreSQL db with all upper-case name

We've been trialling ArcGIS at work and are just about to buy. However, we have a PostgreSQL database which contains a lot of stuff we work with, which is named in all upper-case ("GISSTUFF", for ...
0
votes
1answer
69 views

PostGIS cannot create extention:ERROR: cache lookup failed for function 1

When trying to create PostGIS extension I am getting this error: ERROR: cache lookup failed for function 1 I am running PostgreSQL 11 on Ubuntu 16.04. I have installed PostGIS and PostGIS scripts. ...
0
votes
0answers
45 views

Cache lookup failed for function 1 while activating PostGIS

I'm using PostgreSQL 10.5 on a remote server (Debian). I created a database inside then I attempted to enable PostGIS but it wasn't successful. I had this error : cache lookup failed for function 1
1
vote
2answers
81 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 ...
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: ...
1
vote
2answers
51 views

Smooth Edges of polygon in postGIS

I have a shape that looks like this: It's the end result of a buffer, merge, bugffer and Chaikin Smoothing process in PostGIS. It's almost perfect however I'd like to remove some of the "fissures". ...
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 ...
2
votes
1answer
88 views

Better performance for geohash aggregation with PostGIS

I'm using the GeoNames (https://www.geonames.org/) dataset and want to aggregate the points in geohash of a specific precision. Beforehand I'm filtering with an bbox. So this is the query I came up ...
0
votes
1answer
56 views

How to cast Geometry to Geography in PostGIS/Postgres with sqlalchemy + geoalchemy

Some PostGIS functions behave differently based on the arguments types. Example: ST_Distance will calculate projected units distances for geometry arguments and meters distances for geography ...
0
votes
0answers
29 views

How do I load geojson using php into postgres

Translated I am new to coding and for one of my classes I have to make a map with markers. I have a database but I can not make it a geojson code to be able to display this data on my map. When I ...
0
votes
1answer
31 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
21 views

What is stored in Options|Advanced|PostgreSQL|connections|dbname|keys group in QGIS 3?

Since QGIS 3 in the Options|Advanced|PostgreSQL path referred to in the title there is a new group 'keys' as can be seen in this screenshot: In old QGIS 2.X: What kind of information is stored in ...
1
vote
0answers
44 views

Extracting all cities from a state using PostGIS

I have written a PostGIS query on a database with OSM data of the state of Baden-Württemberg. SELECT * FROM public.planet_osm_polygon WHERE boundary = 'administrative' AND admin_level = '8'; So ...
1
vote
0answers
34 views

Shortest path with certain points

I have a warehouse that has many storage yards. I have an order. It has items list and I have to collect them from storage yards. Storage yards are not equal each other. Their length are different ...
1
vote
1answer
115 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
1answer
46 views

Improve performance of ST_Extent query

I am trying to make my own vector tiles and I am using Mapbox Studio Classic for that purpose. When I export my dataset, Mapbox Studio will try to figure out the extend of my data and those queries ...
1
vote
1answer
62 views

PostgreSQL PostGIS: append table to another table [closed]

I have a large polygon table (30+million rows) and a smaller polygon table (45,000 rows). Both are PostGIS enabled. The smaller table only contains polygons that do not exist in the large table. Both ...
1
vote
0answers
38 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. ...
0
votes
1answer
31 views

left join returning one more row than expected

I have a table of single polygons (table rast_poly) derived from a raster and a table of polygons that represents buffered points (table pt_buff). The raster polygon geometry has been reduced by using ...
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 ...
0
votes
0answers
55 views

ArcGIS Pro connection to PostgreSQL Database

I am connecting a PostgreSQL database to ArcGIS Pro and it connects but when I attempt to open the attribute table I receive a "Shape Integrity Error" and cannot view it. I am able to view the ...
0
votes
0answers
27 views

How do I create a view across a server using postgres_fdw?

My understanding is that you can query across servers if you use postgres_fdw. I have set this connection up successfully following the official documentation. However, I am struggling to construct ...
1
vote
1answer
54 views

PostgreSQL PostGIS: Spatial join but keep all features that don't intersect

Spatial joining a point table to a polygon table, creating a new output table. So far I can only get the output table to contain features that satisfy the spatial join. However, I want the output to ...
1
vote
3answers
68 views

How to find the closest polygons to a point

I am trying to the find the four closest building polygons to a junction point layer. I understand how to obtain this for a single point but am unsure of how to specify the four closest polygons for ...
1
vote
1answer
28 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
1answer
17 views

Geometry from Record use in Function

I have a function I am creating for some iterative task I need to do but I cannot get the geometry coming out of a record data type into the execute properly: DO $$ DECLARE records record; images ...
1
vote
1answer
47 views

PostGIS SQL Query Filter by Bounding Box

I have the following query on a PostGIS Database SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM ( SELECT 'Feature' ...
1
vote
0answers
21 views

What is the difference between pgr_createtopology and pgr_createverticestable?

I'm using pgRouting and I'm going through the manual (Release 2.1.0) and in chapter five, it says: • pgr_createTopology - to create a topology based on the geometry. • pgr_createVerticesTable - to ...