Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

554 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
91 views

Recursive query

How can I do a recursive query with these 2 queries: SELECT ptb.* AS pois FROM ways way, pois pta, pois ptb WHERE way.id = 1 AND pta.id = 12 AND ST_DWithin( ST_LineSubstring( way.path, ...
1
vote
0answers
49 views

Storing custom QGIS form in PostgreSQL

I would like to store my custom .ui form in the QGIS style saved in PostgreSQL, so I can load it within the layer. The option is here when you save the style, but I can't see it stored on the ...
1
vote
1answer
83 views

Turning a single MultiPolygon into many small polygons?

I have a large GeoJSON file with a single very complex MultiPolygon, and I would like to turn it into an identical file with many small polygons. How can I do this? I can use ogr2ogr or mapshaper or ...
1
vote
1answer
101 views

PostGIS geocoding with local street centerline data

I have the PostGIS TIGER geocoder setup and working to geocode addresses in a single city. I am trying to use local street centerline data for geocoding instead of the TIGER data because it is more ...
1
vote
0answers
65 views

Setting PostgreSQL privileges for ArcGIS?

I'm migrating from SQL Server and setting up my first PostgreSQL server for use with ArcGIS. I've followed esri tutorials and related how-tos. I'll be using a versioned editing environment, as I ...
1
vote
0answers
24 views

parse_address Error when using Street2 splitter '@'

I'm trying to use parse_address to parse client data and am finding that the split code "@" to tell it where to split address 1 and 2 gives erroneous results when used. If used, the street number is ...
1
vote
1answer
76 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 ...
1
vote
0answers
43 views

PostgreSQL ST_LineLocatePoint function

I am using the ST_LineLocatePoint function from PostgreSQL. According to what I have read, the description for this function is as follows: Returns a float between 0 and 1 representing the location ...
1
vote
0answers
91 views

Query for finding intersection points between adjacent features within a buffer tolerance - PostGIS

I've developed the queries to find out intersection points (within 0.2 buffer) between line and polygon and also within adjacent polygons by using PostgreSQL 10 and PostGIS 2.4 database. Line data is ...
1
vote
0answers
87 views

PostgreSQL Views with geometry on QGIS

Not entirely sure what kind of rules does QGIS need to consume SQL views So I've had some success in using an id column and a geometry column but now that I'm doing something a bit more complex, it's ...
1
vote
0answers
29 views

Raster Average Across Tiles

I have a raster that is tiled and I am looking to produce an average raster with a 3x3 window. I have tried three methods. The first method uses the standard ST_Mean4ma. An average raster is produced, ...
1
vote
1answer
80 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 ...
1
vote
1answer
38 views

Why does my source and target repeats itself when I use pgr_createtopology()?

I created my path in QGIS v2.18.18, making the lines and a combination of cleaning tool and explode lines tool to get my desired output. I tried using ROAD GRAPH plugin in order to check whether my ...
1
vote
0answers
232 views

Shapefile import failed in PostGIS

I am new to PostGIS/PostgreSQL and have downloaded and installed to perform a spatial join which ideally takes 27 hours in ArcGIS Pro and 19 hours by ArcPy.Hoping for a faster results via PostGIS from ...
1
vote
0answers
78 views

rt_raster_from_two_rasters: The two rasters provided do not have the same alignment

I have a set of geometries in my database which I used to create a set of rasters (each raster has a unique color). Now I would like to merge all of these rasters into one but get an error such as : ...
1
vote
0answers
91 views

Create raster layer from points with PostGIS

I'm pretty new to this part of GIS; I have a layer of points that represent cell phone towers; with a given distance to simulate coverage, how can I transform that represented coverage into a raster? ...
1
vote
1answer
105 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 ...
1
vote
0answers
93 views

Is there a function to obfuscate a PostGIS point or geometry?

I am looking for a function, perhaps a Perlin noise implementation or something similar, for PostgreSQL/PostGIS. Does such a thing exist in core PostGIS? If not, does such a function exist an ...
1
vote
0answers
63 views

Uploading overlapping contours into PostgreSQL?

I've got 100+ shapefiles of contours with the following fields: class (text) altitude (double) geom (geometry) And I need to upload them into a single table in PostgreSQL. Now, I've managed to ...
1
vote
0answers
44 views

ArcGIS PostgreSQL Trigger

I currently have a trigger defined in my database which executes when my edits in ArcMap are saved using the AFTER INSERT ON method. Is there anyway to execute this trigger without having to select ...
1
vote
0answers
99 views

Optimal path proposed by pgr_astar is not the optimal one?

I'm using the function pgr_astar function of PostgreSQL to find the optimal path between two points in the dataset of Chicago. The query I'm using is: SELECT a.seq, a.id1 AS node, a.id2 AS edge, b....
1
vote
0answers
37 views

pgr_astar function does not accept negative values?

I'm using postgreSQL and I'm running the pgr_astar function to get the optimal path. I calculated my path using a formula that uses angle of elevation or the angle of depression as input. So I'm ...
1
vote
0answers
71 views

Converting .pbf to PostrgreSQL table using osm2pgsql?

I try to import a .pbf file into a PostgreSQL table using osm2pgsql in python using the subprocess module. The command works perfectly in the command-line, but when I try to use it in subprocess it ...
1
vote
0answers
29 views

Load shapefile in PostgreSQL with intersection rule

I have one segmentation shapefile vector that I would like to load in a PostgreSQL database. However I need to remove the polygons created in background values of landsat image. Since I have a big ...
1
vote
0answers
27 views

Refer to previous edges in PGRouting

I would like to write a cost functiont that takes into account the previous edge, rather than only the current one, to calculate for instance: Route with the smallest number of turns For that I ...
1
vote
0answers
42 views

Trigger history_ How can I define the last updated column?

I am trying to implement a trigger function in PostgreSQL. I've written this trigger history function: CREATE OR REPLACE FUNCTION history_update() RETURNS trigger AS $$ BEGIN INSERT INTO ...
1
vote
0answers
60 views

Export label to dxf from postgresql by ogr2ogr

I need to export label to dxf from postgresql by ogr2ogr command, label information is text field in PG, but it is always unsuccessful, I always lose the text label information. Those commands are ...
1
vote
1answer
171 views

Different SRID shown in QGIS and PostGIS for the same data

I have data stored in PostGIS. When I view its properties in QGIS, it says: SRS: EPSG:4326 - WGS 84 However, when I want retrieve the SRID value directly using the Find_SRID function, it returns 0. ...
1
vote
0answers
177 views

Duplicate geometry column in geometry_columns

Dear GIS stackExchange community. I've a Postgres/Postgis table with once geometry columns. When I connect it to QGis I see two same column : If I make a request in Postgres select * from ...
1
vote
0answers
279 views

Unique constraint on geometry column in PostgreSQL/PostGIS

I have a table of points, and I set some time ago a simple unique constraint as such : ALTER TABLE points ADD CONSTRAINT points_geom_key UNIQUE (geom); It generally worked fine, until now that I ...
1
vote
0answers
178 views

Piping data between PostGIS 'raster2pgsql' and PostgreSQL using Python subprocess?

My goal is to create a system that will take a GeoTIFF format raster image and programmatically import it into a Postgres table using PostGIS raster2pgsql. In order to get this done with minimal ...
1
vote
0answers
418 views

Can't connect with my PostgreSQL database with ArcGIS

I have created a database in PostgreSQL, spatially enabled it, and now I need to connect it with ArcGIS (we use only ArcGIS products here on my university), everything on localhost, I've tried ...
1
vote
0answers
82 views

Cannot generate mapnik tiles on Fedora

I have gone through the following tutorial to start a map server on Fedora 28: https://wiki.openstreetmap.org/wiki/Mapnik/Installation_on_Fedora_18 Although the tutorial is a bit old, most of the ...
1
vote
0answers
68 views

Is there any way to read raster path in POSTGRES and bring to QGIS screen

.qlr or .vrt files contain a list of raster files path such as : 'C://raster/NYK.tiff'....'C://raster/CAL.tiff'.... etc... QGIS displays them by reading the embedded paths. I would like to store ...
1
vote
0answers
218 views

PostGIS Optimising ST_Contains

I have a table of points called features with 57k points A table of small polygons called lsoa with 36k polygons A table of large polygons called la with 316 polygons covering same area as lsoa All ...
1
vote
1answer
2k 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
61 views

compress error arcgis multiuser database postgis backend

arcgis 10.5.1, postgresql 9.5 using PG_Geometry backend. this database is working fine but I have some spatial tables(most are views..not sure why) that do not have an SRID as seen below when I try ...
1
vote
0answers
164 views

Differences between ST_ClosestPoint and ST_LineInterpolatePoint?

As a bit of context : my exact goal is taking the set of points (some GPS data) and correcting their position so that they intersect some lines (OSM roads data), to have them nicely sitting on the ...
1
vote
0answers
77 views

Unable to edit geometry into database using PostGIS connection in Civil 3D

I have prepared a set of tables (in PostgreSQL) in database with unique geometry types (point, line, polygons) for survey work. I am using Autodesk Civil 3D to edit the geometries in the tables but ...
1
vote
0answers
39 views

Extracting .osm from PostGIS database

How can I extract .osm file from PostGIS database? I've been trying Osmosis: osmosis --rp database="gis" user="postgres" password="123" --wb gives me an error: org.openstreetmap.osmosis.core....
1
vote
0answers
42 views

Setting ArcGIS field access restrictions?

I am using ArcGIS 10.5 and creating features class in a PostgreSQL 9.5 database. But some feature classes have specific fields that some users should not edit. For example I have a "gas pipes" feature ...
1
vote
0answers
77 views

Add jpeg2000 in a postgresql DB

I am trying to add a jpeg200 image to my postgresql database. Problem: this format is not implemented by default in postgresql/postgis which uses the gdal library. After some research, I found that ...
1
vote
0answers
242 views

should ST_Centroid and ST_Transform be applied in a particular order?

I am a complete newbie using GIS, and particularly using postgresql-postgis. I have a table with administrative boundaries where limits are multipolygons defined in utm (EPSG:23030). I want to ...
1
vote
0answers
172 views

ESRI Polygon cutouts (neg. space) become multiple layers in PostGIS

I have a single MULTIPOLYGON from ESRI ArcGIS with 10,000+ sub-polygons in a Shapefile, some having > 1.4 million vertices. I'm importing the data into PostGIS (ogr2ogr or shp2pgsql, same results) so ...
1
vote
0answers
115 views

Edit attributes in relation

I have made a 1 / N union using the QGIS tool between an attribute table in CSV format and a shapefile using a common field(N). The .csv attribute layer has the following field structure: ID - N- T ...
1
vote
0answers
24 views

Optimal Way to Assign Most Popular Neighbor Variable in PostGIS

I've got a census block table in Postgres with the appropriate geom column and a string variable. All census blocks that have a population greater than 0 have an assignment for my string variable. ...
1
vote
0answers
428 views

shp2pgsql throws “ERROR: current transaction is aborted, commands ignored until end of transaction block”

I am trying to upload a shapefile to postgres using the command : shp2pgsql -I -s 4326 "/Users/abc/Landmark.shp" table_name | psql -U user_name --dbname=db_name I get the error : ERROR: ...
1
vote
0answers
103 views

Failure in importing geojson data in Postgresql

I am new to both PostgreSQL and GeoJson. While following some of the tutorials online and some answer over here I wrote this line to import my geojson file: ogr2ogr -f "PostgreSQL" PG:"dbname=In user=...
1
vote
0answers
268 views

Using function in pgPointCloud extension to PostgreSQL with PostGIS

I am using the pgPointCloud extension to PostgreSQL and have created a table of blocks/patches of points in a point cloud. Now, I am just trying to run a few of the functions in the pgPointCloud/...
1
vote
0answers
434 views

Error when reading Layer from .gpkg with readOGR into R and writing it to PostgreSQL

About the SessionInfo() I work with: R version 3.4.2 rgdal version: rgdal_1.2-13 sf version: sf_0.5-4 The problem: Recently I started to work with Geopackages .gpkg in R. The goal is to read a ...