Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

0
votes
1answer
17 views

How to run pg_dump for many tables

I have many tables in my database and I want to back up them one by one in separated backup files, avoiding to right click on each table in pgadmin and avoiding to retype the password. How should I ...
-1
votes
2answers
44 views

How best to divide line features into equal parts and create continuous buffer zones for them using Postgres/PostGIS and QGIS [closed]

Having familiarized with an similar question specified on the link Dividing a buffer into segments based on path and distance, the author asked an analogous question whether it is possible to solve ...
-2
votes
1answer
46 views

Can I use PostgreSQL as a database with ArcGIS Pro? [closed]

I've been with my current job for 2 years now and have been using QGIS with PostgreSQL. We are currently testing ArcGIS Pro noticed that I can connect to my PostgreSQL database that I was using on ...
0
votes
0answers
24 views

GeoAlchemy2: Cannot insert row because Function st_geomfromewkt(unknown) “does not exist”

i've been stuck on this problem for quite a while now and I can't figure out what could be the issue here. I have a Postgres DB with postgis extension installed CREATE EXTENSION IF NOT EXISTS ...
1
vote
0answers
54 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, ...
0
votes
0answers
29 views

Filter Nominatim data by Class or Type

Is it possible to prevent certain results based on "class" and/or "types"? For example, I do not want any results that have the following class or types: class: natural, highway type: peak, footway, ...
1
vote
0answers
34 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 ...
2
votes
1answer
41 views

Cleaning topology errors in PostgreSQL (vector data) using QGIS?

I need to append the shapefiles to a PostgreSQL database. Once the data gets appended to PostgreSQL, some topology errors like gaps & overlaps can be seen. By using the v.clean tools the topology ...
0
votes
0answers
18 views

Using OS authentication for users with period in name when editing PostgreSQL database from ArcMap?

I am trying to set up a PostgreSQL server to integrate our active directory credentials. I can edit the pg_hba.conf file to allow users with an internal IP to connect with OS authentication. host ...
0
votes
1answer
50 views

How to give a table some geometry from a view on QGIS using a join?

So basically I've got this static table that people update information Then I need it to give it some geometry from a view table. This geometry is dynamic, that's why can't just make a new geom ...
0
votes
1answer
20 views

Update geometries with ST_UnaryUnion at TRIGGER

We are currently working on database with multipolygons and we're using QGIS 3. Sometimes it's very useful to work with parts (each page of document is separate part of multipolygon), however at the ...
1
vote
1answer
59 views

Very slow loading of PostGIS raster layer in qGIS

I'm trying to use big raster dataset with PostGIS 2.5 on PostgreSQL 11 on Linux for the purpose of serving a base map organization-wide. My dataset is 2TB of TIFFs. I created 8GB test table (including ...
0
votes
1answer
32 views

PosGIS KNN returns different results from QGIS hub spoke function

Following example here I have tried to get nearest neighbours where code is; CREATE TABLE model.hub_spoke_spokes3f AS SELECT hub.id AS hub_id, spoke.id, spoke.point_geom, ST_Distance(hub....
0
votes
1answer
20 views

ST_contains between two tables returns all results of one table multiplied by the number of results from other table

I have one PostGIS table of polygons like this: polygon_id | polygon_geom And another of points like this: point_id | polygon_id | point_geom I want to find all the points from the points table ...
1
vote
2answers
68 views

PostGIS intersects and case function

Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons ...
0
votes
0answers
47 views

QGIS 3 Plugin with cascade list runs slower each time I launch it

I'm writing write a plugin for QGIS. Its first step is to select an item after having selected 'parents items', in a cascade selection. It works, but it is getting slower and slower after a few ...
1
vote
2answers
37 views

How to convert MultiLineStringZ object to MultilineString object?

I have CAD-data which I transformed to shapefiles. Currently after the transformation the objects still have a Z value which is useless to me. My question is: How do I convert a MultiLineStringZ-...
0
votes
1answer
29 views

Fetching the geometry and attributes from PostgreSQL

I have 2 fetching administrative areas one inside the other. For that, I am having servers to get the data. When I am running anyone query in SQLCreator, it is working fine separately, but while ...
1
vote
0answers
76 views

PostGIS - Dynamic Server-Side Clustering based on Zoom level

I'm working on a Ruby on Rails application that is showing road signs on the map(leaflet). So, user can browse map and road signs, great. But, if there are a lot of signs and user wants to zoom out, ...
1
vote
0answers
36 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 ...
0
votes
1answer
23 views

Getting OGR to work on a Mac

I downloaded GDAL on my Mac (running Mojave 10.14.1) through Homebrew and I'm having trouble getting OGR to work with Postgresql. When I try the following to create a geojson from gpx files I have, I ...
3
votes
1answer
51 views

How should arrays be stored in PostgreSQL when used as a datastore with GeoServer 2.14+?

I am storing a string array in PostgreSQL to serve through WFS in GeoServer 2.14.1. As a test, I have two columns in my table; one column is a character varying array and this other is a text array ...
1
vote
0answers
27 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 ...
0
votes
1answer
27 views

shp2pgsql creates one dimensional shapes

I use shp2pgsql to import an .shp ESRI shapefile into PostgreSQL database. I use brew installed shp2pgsql. I used this command first: shp2pgsql -I -s 4326 /path/to/shape_file.shp public.os_gb | psql -...
2
votes
1answer
39 views

LineLocate a sequence of points, preserving order

I have a linestring, which represents a bus route, and a sequence of points in order, representing the coordinates of each stop of that route. It kinda looks like this (pardon my poor drawing skills): ...
1
vote
1answer
28 views

ST_Intersects one polygon with layer - always returns 0 results

I'm trying to write a query to find if any features in a layer intersect with a certain polygon, but it is always returning 0 rows. I've tried messing with SRIDs, different geom types, sizes, shapes, ...
1
vote
0answers
18 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
0answers
41 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 ...
8
votes
1answer
346 views

How can I optimize a Point in Polygon query for millions of points when most of the points lie within the polygon?

I have 150 million points in a point table and would like to find the few points lying outside a given polygon geometry. I know that 99.9% of the points are within the polygon geometry. I am ...
1
vote
0answers
17 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
1answer
22 views

Why does rescaling by 1.0 speed up this PostGIS query

To preface, I'm not sure if it matters, but I'm using PostgreSQL 11 with PostGIS 2.5 (through Docker on Windows) and accessing it with Python through PsycoPG2. My raster has the recommended index on ...
4
votes
2answers
45 views

OSM Line 'way' from PostgreSQL (PostGIS) to Java Linestring (from JTS library)

I would like to read the data from PostGIS OSM database to Java object. I have 'planet_osm_line' table. I'd like to get 'osm_id' and 'way' column read by ResultSet, as here: stmt = con....
0
votes
2answers
62 views

Connect QGIS to Esri geodatabase hosted on RDBMS (PostgreSQL)

I have an ArcGIS 10.5.1 enterprise geodatabase hosted on PostgreSQL 9.4 (non-PostGIS enabled, but with SDE.ST_Geometry support). I'd like to add some feature classes as layers in QGIS. How can I do ...
3
votes
1answer
147 views

PostGIS ST_Intersects is slow and doesn't seem to use spatial index

There are several threads about ST_Intersects being slow (like this one). Main advise is to use spatial index. Well I know that and created spatial index beforehand, but the query is slow nevertheless ...
1
vote
0answers
58 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 ...
0
votes
1answer
78 views

Finding self-intersection in PostGIS

I've developed an query to find self-intersection points by using PostgreSQL 10 and PostGIS 2.4 database. The query took long time (aprx 90mins) to produce the result from 3,122 polygons. The query is:...
1
vote
1answer
36 views

Trigger - Calculate area of a layer inside feature

I'm trying to create a trigger to auto-fill a field of a table in postGIS based on two polygon layers geometry. I'm using QGIS for geometry updates. The first layer (lotes) has bigger polygons and ...
1
vote
1answer
39 views

Splitting multilinestrings in segments using PosgreSQL

I need to split several multilinestring into a defined number of segments using PosgreSQL. I tried using ST_subdvideand ST_segmentize but they didn't work. Can someone help me to solve this problem? ...
0
votes
0answers
39 views

How do you change the value of a Boolean column in a QGIS 3 Attribute Table?

I am editing feature in a PostgreSQL table in QGIS 3 and while I can edit the values in most columns, I cannot edit the Boolean value. I can enter the cell in edit mode, but keystrokes don't work. I'...
-1
votes
0answers
18 views

Problem by Enter Credentials when adding postgis to qgis

Yesterday, I downloaded Postgresql, I followed the instructions of some tutorial, set username, password etc... I would to connect qgis to postgis, from layer menu, add postgis layer, new connection, ...
0
votes
0answers
45 views

PostGIS DISTINCT ON clause

I have two tables essa -- thousands of polygons narrowed -- few hundred parcels My goal is to find the closest parcels to each essa polygon. it happens to be many of the parcels are the closest ...
1
vote
0answers
75 views

QGIS 3 Python plugin psycopg2 Postgres Notify/Listen Live Connection

I'm developing a plugin for QGIS3 for my company, where my managers can allocate tasks to other users. I have programmed two tables within the python script, witch will be only visible according to ...
-1
votes
0answers
30 views

Virtual Fields in PostgreSQL 10.5

I'm having trouble getting a virtual field imported from QGIS 3.0 to PostGIS. The other fields in the layers are importable to my database. We need it in PostGIS to get multiuser-access on the it. The ...
0
votes
1answer
44 views

Can I copy a PostgreSQL/PostGIS database to a file? [closed]

I want to have a full copy of a PostgreSQL/PostGIS database from a server to my portable usb/hard disk and then transfer it into a new server that I will create. Is this possible?
1
vote
2answers
49 views

PSQL asks for a password [closed]

When I pipe the output of raster2pgsql to psql -d ais -U postgres -h localhost -p 5432 it asks for a password. How can I supply it the password on the command line?
0
votes
0answers
68 views

ArcGIS vs QGIS vs PostGIS projection issue

Windows 10, ArcGIS Pro, QGIS 2.18, pgAdmin 4-postgreSQL I have two (sample area) PostGIS tables in a PostgreSQL database: building_polys (SRID: 27700) -OS MastermapTopo address_points (SRID: 4258) -...
2
votes
0answers
115 views

PostGIS queries result in “Symbol not found: __ZN5boost6system15system_categoryEv”

I seem to have lost the ability to use basic postgis queries. Normal postgres queries are unaffected. Using Mac (High Sierra), using postgres 10.5 / postgis 2.4 (probably) The same error happens ...
0
votes
1answer
26 views

CONVERT lat and long field in PostgreSQL using ST_GeomFromText

i have two fields for lat and long in point text record.how can i use ST_GeomFromText function for all of my records to convert it into geometry??
2
votes
1answer
53 views

Select layer by location in PostgreSQL

I have a footprint of some buildings (see the picture). The selected buildings have an area below 30 sqm. As you can in the picture see some buildings share almost borders with a building bigger ...
2
votes
1answer
45 views

List table names within PostGIS bounding box

So I have a bounding box drawn by the user on Leaflet from which i can get features of specific table as follows SELECT * FROM $table WHERE $table.geom && ST_MakeEnvelope($test, 4326) where $...