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
38 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
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 ...
1
vote
0answers
36 views

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

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
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....
1
vote
0answers
27 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
32 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
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
39 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 ...
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
42 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
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
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
53 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
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. ...
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 ...
1
vote
0answers
11 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
50 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
60 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
40 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
79 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
48 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
83 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
48 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
25 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
110 views

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

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
44 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
30 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
205 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
54 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
53 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
67 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
26 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
46 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
20 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 ...
1
vote
1answer
38 views

PostgreSQL: Spatial join points to polygon, listing point attributes where multiple are joined

I have a large polygon table and a large point table in a postgis enabled PostgreSQL database. Each point has a unique reference number (double type). I want to spatial join the points to the polygons,...
4
votes
1answer
45 views

Exporting layer to PostgreSQL / Inserts per Transaction

I am exporting layers to a PostgreSQL 11 database with PostGIS extension. The export itself is taking a while for big datasets (let's say 50.000 points). As I see in the pgadmin dashboard there are ...