Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
1answer
18 views

Insert empty VIEWS into QGIS from PostgreSQL using PyQGIS

The Aim: I am trying to insert a number of empty VIEWS into QGIS using PyQGIS. These VIEWS have been created and stored in a PostgreSQL database. I want to also ensure i can successfully re-open ...
0
votes
1answer
49 views

PostGIS servers to increase power

I am currently working locally on my terminal. I have Windows 10 x64, 12Go RAM, 428Go SSD, an Intel(R) Core(TM) i5-6200U CPU @ 2.30GHz, 2400 MHz, 2 hearts. I need to use PostGIS to make some queries ...
3
votes
0answers
27 views

Computing driving directions in pgRouting/PostGIS?

I have calculated shortest route/path by using pg_Dijkstra algorithm in PostGIS and it is working fine. Now how to calculate driving directions from starting edge to end edge. I have gone through the ...
3
votes
1answer
698 views

Replacing pixel value by lookup table using PostGIS?

I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table. For example, suppose I have ...
3
votes
2answers
5k views

PostgreSQL/PostGIS: create spatial view

I have a polygon layer and a point layer. 1) I wrote a spatial query to count how many points fall in each polygon: SELECT polR.codeR, count(pntAZ.geom) as tot_pntAZ FROM boundaryR as polR LEFT ...
0
votes
1answer
42 views

PostGIS- convert points to polygon based on multiple shared attributes

I have a PostgreSQL PostGIS point table (not multi-points). I want to convert the points to polygons (using convex-hull?) grouping the points by shared attributes from 2 columns. In this example, '...
4
votes
4answers
4k views

Querying points inside and outside of polygons?

In PostGIS I have two tables: Table that contains polygons: table a.provinces gid | geom | name | ------------------------------ 1 | 010604 ... | Champagne | Table that contains ...
15
votes
3answers
7k views

Add PostGIS spatial functions to a custom schema other than “public” in PostgreSQL

Recently, I created a PostGIS 2.0.3 database on a PostgreSQL 9.1 database server using pgAdmin. The "PostGIS" extension was found installed in "Extensions". Al spatial functions were added to the "...
1
vote
1answer
70 views

Removing same name layers from layer table in geotool?

I am making a geotool application in which I read the data from postgresql database. In this application if i make a buffer on a selected polygon then a buffer is created and buffer layer shown in ...
2
votes
2answers
100 views

How to load the geometry field of a feature class to PostgreSQL?

I am trying to load a polygon feature class in a file geodatabse to a PostgreSQL table (extension PostGIS installed). This the process I'm following: I create table in PostgreSQL with required fields ...
2
votes
1answer
30 views

Unable to load geometry field from geodataframe to a PostgreSQL table 'geometry' field

I am trying to load a geodataframe to a PostgreSQL table, but I am struggling with the geometry field. First I read feature class using Geopandas: geodata = (r'path_to_gdb', driver='FileGDB', layer='...
0
votes
0answers
27 views

Creating direct connection between QField app and PostgreSQL? [closed]

Does anyone know of any tutorials / guides for creating a connection between QField mobile data collection app and PostgreSQL? So that data collected in the field via the app can be saved directly ...
0
votes
0answers
34 views

Transfer indexed Oracle geoloc table to PostgreSQL using ogr2ogr

I am copying tables using ogr2ogr from Oracle to PostGIS using ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME&...
1
vote
0answers
62 views

PostGIS: proper way to model object that follows sections of another object's path?

First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words. I'm trying to model aerial utilities in a PostGIS ...
1
vote
1answer
28 views

How to set schema parameter in osm2pgsql calling

How I can to set schema name parameter when call osm2pgsql? For example: osm2pgsql --create -s -C 2500 -G -S D:\default.style -d osm_db -o input -U osm_user -W -H localhost -r pbf D:\osm.pbf I set ...
1
vote
0answers
15 views

Oracle to PostgreSQL migration with ogr2ogr only imports first geoloc column

I transfer Oracle tables to PostGIS using Oracle Wallet and ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME>...
1
vote
0answers
25 views

Combine data of OSM roads into longer lines using PostGIS

For my vector map I'd love to display highways at a quite early zoom level. The problem is that this data would be quite big, so I thought I simply ST_Simplify it. The problem though is that the OSM ...
0
votes
0answers
24 views

Can't save projects in PostgreSQL 9.3 from QGIS 3.4.5

I didn't find any answer about my problem elsewhere. I can't save any project in my PostgreSQL database. The message says "Impossible to save the project because the destination table on the ...
1
vote
0answers
257 views

Given an array of OSM point IDs, how can I create a path of connected roads?

I'm writing a shortest route script in Python using geospatial data from a Postgres DB with PostGIS extension and pgRouting to make the points from OSM data. I have a path of OSM IDs by points. I ...
1
vote
1answer
111 views

psycopg2.ProgrammingError: function does not exist

I am using Python code cur.callproc('function_a',[parameter1, parameter2]) for executing one function and this code is correct when I use a user having full DB permissions. But when I use a private ...
1
vote
0answers
26 views

Whole schema migration from Oracle Spatial to PostGIS import using ogr2ogr

I can migrate Oracle SQL table to PostgreSQL using ogr2ogr using: ogr2ogr -progress -f "PostgreSQL" -nln schema.table -skipfailures -overwrite -lco FID=ID_COLUMN -lco GEOMETRY_NAME=geom PG:"host=...
2
votes
1answer
149 views

Automatically add points geometry while GPS logging?

I have a PostgreSQL 10 Server (x64) running on my Windows 8 (x64 based) machine. On the top of my Postgres database, there is PostgREST API running that receives GPS data (5 seconds interval) from a ...
1
vote
1answer
63 views

PostgreSQL: Copy the line segment together in another table without copying the intersection with another line segment

Presently these linesegments are in different rows but in the same table and numbers shown in the image are the ids of respective linestrings. I want to copy linesegment 350, 333, 352,4,.. in one ...
3
votes
1answer
812 views

Calculating total line lengths within polygon?

I have basically the same conundrum as Calculating line segment lengths within polygon using ArcGIS Desktop? but I'm working in PostGIS, not ArcGIS. Any pointers? This query seems to like it should ...
1
vote
3answers
44 views

PostgreSQL/PostGIS error “pseudo-type record”

I've got, in PostgreSQL/PostGIS, create table datos ( gbifID int primary key, orden varchar (50), eventDate varchar (50), geom geometry); When I run select distinct (geom,eventdate), geom from ...
4
votes
1answer
205 views

ST_LineMerge to simplify road network

I am trying to simplify my road network by finding all segments that touch 2 other lines with a common attribute, join_id and street, then doing a st_linemerge. I do NOT want to merge lines where ...
1
vote
0answers
33 views

Trigger on PostGIS line layer occurs twice [closed]

Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, ...
1
vote
1answer
27 views

Combining st_snaptogrid with st_centroid and group by using PostGIS?

I have a table of devices, each of them has a field 'location' that is a postGis geometry(Point,4326) I need to operate a GROUP BY based of the placement on a grid, I'd use ST_SnapToGrid and then I ...
2
votes
0answers
171 views

QGIS is not working with Postgres Plus Advanced Server? [closed]

We have a problem to integrate GIS software with our GIS database which is populated on Postgres Plus Advanced Server 9.2 from EnterpriseDB. We are using QGIS to maintain and analyze our GIS ...
1
vote
1answer
22 views

Migrating SDO_JOIN to PostGIS

I have a query in Oracle to select polygons from my_polygon_table that interact or reside inside of a single polygon residing in border_table: Select t1.*, t2.*, sdo_geom.sdo_intersection(t1.geoloc, ...
1
vote
1answer
34 views

Can ogr2ogr read from PostgreSQL materialized view?

I'm writing a quick Python script to bulk export from PosgtreSQL materialized view (PostGIS geometry) to ESRI FileGDB and ogr2ogr (I'm using the one in OSGeo4W through Python subprocess module) is ...
3
votes
1answer
44 views

Add/update shapefile to existing PostgreSQL database in QGIS

I've an exiting state level PostgreSQL database with some layer's and I need to update an additional data to the existing database. Ex: I've a layer called "Road_NW" need to add additional ...
1
vote
1answer
37 views

Pgrouting: Visualizing analyzegraph ring geometries

I have a sample network that I analyzed using pgr_analzyeGraph. The outputs shows that I have 3 ring geometries. How do i get which nodes is part of the ring geometries? The official document only ...
1
vote
0answers
69 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 ...
0
votes
0answers
38 views

Duplicate layer, but in different CRS? [duplicate]

I inherited some data that lines up correctly on the map, but in face are in different coordinate reference systems/SRID. Here is a screenshot example: The CRS of the green layer is: EPSG: 4326 ...
4
votes
1answer
52 views

How to stop QGIS from looking for the wrong PostgreSQL host address in an existing workproject?

It looks like a saved QGIS project workfile may be storing the old PostgreSQL host IP access details. Updates made to the PostgreSQL host IP in the main QGIS window are not being used in the project ...
1
vote
0answers
37 views

Storing raster imagery in PostgreSQL

I have a number of raster files saved locally which I use for basemapping in QGIS. I have built virtual raster fields to load these in one go but I'm looking to store them within my PostgreSQL ...
0
votes
1answer
33 views

SAGA in QGIS toolbox not using files from PostGIS database

I need to find the correct path to use tables and views from a PostGIS database in QGIS. The database was populated importing shapefiles with QGIS DBmanager, and then other tables and views where ...
8
votes
3answers
2k views

PostGIS: ST_Equals false when ST_Intersection=100% of geometry?

I have 2 datasets consisting of cadastral parcel data - roughly 125,000 rows each. The geometry column is WKB polygons representing parcel boundaries; all data are geometrically valid (the polygons ...
1
vote
0answers
19 views

Can't DropTopology due to event trigger

I have a postgres database with one table for each US state. I am trying to make a program which iterates through each of the tables and manipulates the geometry. To do this, at the beginning of the ...
0
votes
1answer
30 views

Using ogr2ogr for converting tables from non-public schema to GeoJSON

I've used ogr2ogr to convert tables in PostgreSQL database to GeoJSON which works fine for the public schema. The question is how can I set the schema to non-public to access my tables? I've already ...
1
vote
1answer
56 views

Importing shapefile from website if download link is compressed file?

I'm trying to write an SQL query with PostgreSQL to download and import multiple shapefiles to tables in my database. All the links are of the form : https://www2.census.gov/geo/tiger/GENZ2010/...
1
vote
0answers
20 views

Minimal Distance Between a geometry and a layer on PostGIS [duplicate]

I am working on PostGIS where I have a two tables. Let's call them A and B. A is coming from a multi polygons layer. B is coming from a multi points layer. I have created a new column "min_distance" ...
1
vote
1answer
52 views

Exporting GeoJSON from PostGIS?

I am, trying to export GeoJSON from postgis. I am using the following code which is working local machine but when I am trying to use this in a server the following error is occuring CODE: ogr2ogr ...
1
vote
0answers
16 views

PostgreSQL Trigger to keep track of table changes [closed]

I am trying to create a trigger (PostgreSQL 9.4) to track changes made to a table. This is my approach: CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS $BODY$ BEGIN IF TG_OP = '...
1
vote
1answer
161 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. ...
2
votes
1answer
31 views

PostgreSQL- query a raster by XY

Windows 10, PostgreSQL 11 I successfully managed to export a .tif raster into PostgreSQL using raster2pgsql: raster2pgsql -s 27700 -I -C -M -Y "D:\Data_Location\country.tif" -F -t 50x50 public....
2
votes
1answer
38 views

Filtering fields from a PostgresSQL/PostGIS database layer in QGIS by Query builder

I incurring in an error while trying to make the following filter of fields in the layer properties Query builder in QGIS 3 from a PostGIS database. select "field_A", "field_B2", "field_D" from ...
2
votes
1answer
31 views

MapServer 7 PostGIS/PostgreSQL support

I'm trying to compile MapServer 7 with: Proj 6 Geos 3.7 Gdal 2.4 PostGIS 2.5 PostgreSQL 10 But for some reason it complaining about pg_config not found, I usually insert the path /usr/pgsql-10/bin/...
15
votes
1answer
5k views

How to properly set up indexes for PostGIS distance queries?

I'm building an application that is supposed to query and return every Record in a table that is X kilometers away from PointX. Records and PointX's positions are determined from (long/lat) ...