Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

566 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
57 views

How to create a function for getting a largest polygon from 2 polygons using case statement in PostgreSQL?

CREATE OR REPLACE FUNCTION public.largest_polygon1( geom1 geometry, geom2 geometry) RETURNS geometry LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ begin Return Case ...
1
vote
0answers
37 views

Using CSW for GeoServer PostGIS database

I am quite new to GeoServer and general OGC fundamentals. I have GeoServer set up and I set up a PostGIS data store which links to a database I have already been able to make successive calls using ...
1
vote
0answers
76 views

Geometry type (Point) does not match column type (LineString)

I'm using PostGIS 2.5.2 on PostgreSQL 10, and I'm trying to build a function which would split a LineString into portions, depending on points positionned close to it. I'm getting a Geometry type (...
1
vote
0answers
34 views

How to speed up PostGIS intersects query?

I have a table which contains 5,00,000 records. I want to fetch all the rows which intersects with the given bounding box. My query is: select * from data_master b where b.geom && '...
1
vote
0answers
80 views

Map Matching between two geometry (line string)

I have two tables in PostGIS, both having line string as the geometry type. I wanted to extract a similar or exact matching street. Can you please suggest query or algorithm for it? Table 1 column ...
1
vote
1answer
67 views

Simple point in polygon query at PostGIS & PostgreSQL

Using PostGIS & PostgreSQL (Amazon RDS instance), I need to create a (quite large) table with points (just x, y coordinates) Then what I need to do is very simple point-in-polygon queries. Points ...
1
vote
0answers
41 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 ...
1
vote
0answers
30 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....
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
68 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
0answers
45 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
0answers
21 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
0answers
324 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
0answers
94 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
37 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
0answers
32 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
0answers
78 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(...
1
vote
0answers
37 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 ...
1
vote
0answers
102 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
42 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....
1
vote
0answers
17 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 ...
1
vote
0answers
97 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
50 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
145 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
119 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
76 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
26 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
90 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
52 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
94 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
92 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
31 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
39 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
303 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
93 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
97 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
123 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
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
48 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
101 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
38 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
81 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
31 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
48 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
67 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
188 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
185 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
307 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
193 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 ...