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.

0
votes
0answers
11 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
1answer
56 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
20 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
40 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". ...
1
vote
0answers
44 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
70 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
19 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
28 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
17 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
42 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
64 views

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

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
36 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
61 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
32 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
179 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
44 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
24 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
51 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
63 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
0answers
20 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
15 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
37 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
30 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
42 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 ...
5
votes
1answer
109 views

PostGIS function to move a polygon to centre over new point coordinates

Background: (Note: I am trying to do as much of this as possible in PostGIS/PostgreSQL) I have a PostgreSQL table containing location data of an aircraft in orbit around a city. Every second a new ...
3
votes
2answers
79 views

Find what polygons are not fully covered by union of polygons from another layer in PostGIS

I have two tables in a PostGIS database >>> select postgis_version(); "2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1" One represents the country borders and another one is administrative ...
1
vote
1answer
105 views

Handling AIS data using Python and PostGIS?

I want to turn points into a linestring in PostgreSQL with the extension of PostGIS using Python. I want the lines in a new table because every few minutes more points are added and it needs to keep ...
2
votes
1answer
29 views

pgRouting assign source and target value to streets based on available nodes

I am a beginner in pgrouting. I have two tables in my postgres database. A street table, where each segment (see red line) carries an Id (ogc_fid). Additionally I have a nodes table (see brown points ...
1
vote
0answers
46 views

line_interpolate_point: 3rd arg isn't within [0,1] strange behavour

I have a strange berhaviour in this postGIS query: SELECT st_length(st_linemerge(v.geom)), c.prog_ini, c.prog_fin, c.prog_ini/st_length(st_linemerge(v.geom)) as ini, c.prog_fin/st_length(...
0
votes
0answers
24 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
2answers
134 views

ERROR: parse error - invalid geometry (Postgis)

Initially I was storing my column as path and then following this question I changed it to text. I'm trying to convert my column from text to geometry type by running this command: ALTER TABLE ...
0
votes
0answers
50 views

Using the geometry calculator to create lines between points in QGIS 3.4?

I have a postgreSQL database layer (with a postgis extension) that updates every 5 minutes and is creating points of the current location of ships. I can't seem to figure out how to connect the points ...
1
vote
0answers
24 views

QGIS 3.X CAD tools not working with snapping on PostgreSQL layers?

If I create a scratch layer in EPSG 32720 CAD Tools are working fine with snapping options. The vertex are highlighted etc. etc. If I use a PostgreSQL layer, same EPSG, the snaping is working, but ...
0
votes
1answer
57 views

Is it possible to use SELECT ST_GeomFromText( with other SELECT? [closed]

I'm trying to use SELECT ST_GeomFromText() with a value catched from another query: SELECT ST_GeomFromText( ST_AsText( ST_GeomFromGeoJSON( SELECT column-name FROM table-name ORDER BY ...
1
vote
0answers
54 views

GDAL PostgreSQL shapefile import “Unable to find driver 'PostgreSQL”

I have seen a number of posts similar to this but with no resolution. I am experimenting with GDAL commands and am fairly new to it however when using a PostgreSQL database connection I am getting ...
2
votes
0answers
58 views

Implicitly convert Geometry to WKT string in PostGIS

Suppose I have a table defined as follows: CREATE TABLE gtest (name varchar, geom geometry); To insert, I am able to simply do this: INSERT INTO gtest VALUES ( 'Polygon', 'SRID=4326;POLYGON(...
0
votes
1answer
18 views

How to get the rectangular coordinates from list of line string

I have a linestring geometry in my PostgreSQL database. I want to get the corner coordinates of the rectangular bounding box. Example "...
1
vote
1answer
35 views

Filter PostGIS DWithin center point SQL

Is there a way I can remove the center point from my results in a query like the following? (select * from cities where l = true and ST_DWithin(geography(cities.geog), (SELECT geog FROM cities WHERE ...
0
votes
1answer
41 views

Google Cloud PostgreSQL for QGIS

Has anyone used Google PostgreSQL and added it as a data source? I have 2 shapefiles that I need to work on with multiple concurrent users and be able to edit them "offline" and synchronize them ...
0
votes
1answer
56 views

Getting Latitude and Longitude from a Linestring

I have a linestring geometry in my Postgres database. I wanted to extract the latitude-longitude coordinates of each point inside the linestring. Example "...
2
votes
1answer
78 views

Cannot see PostgreSQL-tables in QGIS as a 'non' super user

I have established a connection between QGIS and PostgreSQL. As long as the active user has the 'Super User'check-box enabled, all works well. From the moment I disable it only the Schema's remain ...
1
vote
0answers
65 views

QGIS Plugin: display dialog for entering username and password to connect to the database

I do not want the password and username to be saved in the QGIS project. Instead of standard QGis dialog(QgsCredentialDialog) I want to display "my dialog" for entering username and password. On the "...
1
vote
0answers
21 views

TableToTable_conversion using PostgreSQL and ArcPy

Currently, I am trying to convert a geodb to a table in my PostgreSQL db with arcpy as such. I've tried both with the parcels table being created and the parcels table not being created arcpy....
0
votes
0answers
48 views

Mark Dangle Error for linestring

I will create buffer for 5 meter in start point of line string, If any other line string is within the buffer but not snapped then to mark as the startpoint as dangle error.I have attached the sample ...
1
vote
0answers
13 views

How to create a sub region shapefile from MasterMaps on QGIS?

I have the OS Mastermaps on a PostGIS database and this is connected to QGIS. Mastermaps is visualised well on QGIS with all the layer styles. A region is clipped from PostGIS and created a new ...
0
votes
0answers
28 views

How to handle Update error: Transaction does not have authorization for mytable3:mytable3.5?

I am building collaborative mapping app using openlayers 5.3.0, geoserver 2.14.0 and postgresql 10.4. I want the user to be able to update features using wfs-t but before updating, the user needs to ...