Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
3
votes
1answer
187 views

Upgrading PostGIS and PostgreSQL but running upgrade to PostgreSQL it errors?

I want to upgrade PostGIS(2.0.4 ---> 2.2.3) and PostgreSQL (9.2.4 ----> 9.6) so First I upgrade PostGIS 1、install PostGIS 2.2.3 on postgresql 9.2.4 2、alter EXTENSION postgis 2.0.4 ---&...
1
vote
0answers
30 views

Linking new feature violates foreign check constraint in PostgreSQL database when working in QGIS3

I have set up a spatial database in PostgreSQL in which I have some data/tables that need to be linked by M to N relations. For those occasions I have two tables that contains geometry and a pivot ...
0
votes
2answers
30 views

PostGIS Web service call function

I have spatial function in my Postgres/PostGIS DB that when sent a set of coordinates (Long Lat) returns single value. Whilst I'm not a web developer, i'd like to serve the function up as a web ...
1
vote
1answer
28 views

Mixed SRID Geometries

I'm developing a PostGIS function to return a value when longitude and latitude are passed as variables to the function. CREATE FUNCTION flood_location(long float, lat float) RETURNS varchar(40) AS ...
0
votes
1answer
277 views

Appending columns from a table to my active table?

I am trying to append three columns from srs2016 to srs2013_1 using this sql. INSERT INTO srs2013_1 (the_geom, agency_responsible, description) SELECT the_geom, agency_responsible, ...
4
votes
2answers
35 views

How do I retrieve a single image from an ImageMosaic in GeoServer?

If I have an ImageMosaic in GeoServer containing several raster images that overlap, is there a way to retrieve/display one of the original images? Could I use CQL or WCS to get them by fileName or ...
1
vote
1answer
97 views

ST_MakePoint outside PostGIS

To import data into a PostgreSQL/PostGIS database I need this CSV format: "1,nameOfPoint,0101000020E6100000689DA690152F114090338CFF9B0B4A40,2019-07-22 19:56:03.603+00,2019-07-22 19:56:04.243+00" ...
8
votes
1answer
6k views

How to deal with “ERROR: GEOSUnaryUnion: TopologyException: Self-intersection”?

I'm using Postgres 9.4 and PostGIS 2.1. My table looks like this: ------------------------------------------------------------------ ogc_fid | integer | not null default ...
0
votes
0answers
11 views

ArcMap - Definition query using dates to select current week

I would like to write a definition query expression to get features where a date field is in the current week (starting Sunday ending Saturday) I have difficulties to imagine how using CURRENT_DATE ...
2
votes
1answer
129 views

Query in QGIS is significantly slower, than in pgAdmin

So, I've a table in postgresql, that is joined to other spatial table. If I've run a simple query in pgAdmin, let's say SELECT * FROM table1 WHERE "area" > '5000' and it takes less, than 1s to ...
2
votes
1answer
281 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 ...
2
votes
1answer
150 views

Updating PostGIS on Linux

I have POSTGIS="2.4.3 r16312" on my linux server and I need to upgrade it to POSTGIS="2.4.4 r16526" or any newer version. I use the following: ALTER EXTENSION postgis UPDATE; but it gives me the ...
3
votes
1answer
65 views

How to run a moving window function in a conditional statement in PostGIS for buffer artifact removing?

I try to remove the artifacts which occur when running the ST_Buffer function in PostGIS PostGIS 2.5.1 (See picture). For this case I wrote a function that should loop trough every segment (row) and ...
0
votes
0answers
22 views

Losing connection to the server when using PG Routing function pgr_alphaShapes

I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius. SELECT * ...
1
vote
1answer
166 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
1answer
49 views

Line intersection resulting in offset point

When executing an ST_Intersection on 2 lines, the resulting point is offset. In the attached image, the offset is 0.000027m. I have tried ST_Snap to force it, but this has not changed the points ...
0
votes
1answer
33 views

Populate field with drive time values during isochrone creation in Carto

I'm creating isolines (isochrones) in Carto to show 15, 30, 45, and 60 minute drive times from a set of points in a shapefile. Here is the SQL I've written to create those isochrones in an empty table....
0
votes
1answer
1k 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 ...
1
vote
0answers
38 views

ST contains not working properly with trigger

I have table1, on which people do regular inserts, and table2, that needs to get updated based on what was inserted to table1. table1 has id integer type text ref text geom polygon ...
2
votes
0answers
45 views

Generating inverse polygons inside polygon

I'm designing a PostGIS database with country polygon and many layer polygons of water areas, inside these country polygon. I need to generate inverse polygons to water polygons, that doesn't contain ...
2
votes
1answer
45 views

Summarise duration of points within polygon

I have table with points that are gps coordinates for multiple vehicles. I have another table of multiple polygons that the vehicles travel through. I’ve managed to write a python script that ...
-1
votes
1answer
34 views

Request to help on GIS Query improvement suggestion

Thanks for your support. Could someone please suggest on the below query. One of the query which was created on GIS data is taking a long time and even it is not taking the index as well. I have ...
1
vote
0answers
33 views

Suggestion on table analyze [closed]

Environment: PostgreSQL: 9.5.15, PostGIS: 2.2.7 When analyzing a table containing GIS data, I get this output: ANALYZE SCHEMA.TABLE; NOTICE: no non-null/empty features, unable to compute statistics ...
4
votes
2answers
524 views

PostGIS `COPY` geometry with SRID?

Is it possible to use COPY on a file full of WKT POINTs without dropping the explicit SRID on the destination column? I'm trying to insert several million rows of WGS84 points and am using COPY for ...
1
vote
0answers
97 views

Lizmap - QGIS server - PostgreSQL error layer not valide

l installed Lizmap in Windows 10 but a problem is that I haven't the edition button and can't display layers from PostgreSQL. I have a "service non disponible" with "layer not valide" error in QGIS ...
0
votes
1answer
21 views

How to show road arrow in GeoServer?

I've downloaded OSM file and extracted it to PostgreSQL. Now I have 4 layers: planet_osm_line planet_osm_point planet_osm_polygon planet_osm_roads I added these layers to GeoServer and set SLD file ...
6
votes
2answers
3k views

Moving SpatiaLite Table into PostgreSQL using ogr2ogr?

How can I move a Spatialite table from a Spatialite db to a PostgreSQL db using ogr2ogr?
1
vote
0answers
37 views

Feature Class Update Trigger with Archiving

We are deploying Collector in our institution and for offline work on unversioned data we need to enable archiving. The feature class needs some updates when the user enters new records using the ...
1
vote
1answer
367 views

PHP: Call to undefined function pg_connect() with MS4W for PostgreSQL

I am trying to add dynamic interactions with my PostgreSQL 12 database to my web page with the PostGIS extension that I use with PgAdmin 4. I use an Apache web server that I installed with MS4W. For ...
0
votes
0answers
57 views

QGIS, PostgreSQL, user and roles

I am trying to create a shared server for community, and now stock=k with users privilege and roles, I get an error on QGIS when login with my user account. Steps I did : I revoked all privileges ...
1
vote
1answer
25 views

Replacement for pgr_pointToEdgeNode for pgr 3.0

I am working through the book 'pgRouting: A Practical Guide' and have come across a function that has been deprecated. The function is pgr_pointToEdgeNode. Is there a similar function I can use to ...
1
vote
0answers
138 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 ...
0
votes
1answer
13 views

Trouble working with a .TSV containing WKT

I'm working with a .TSV file that represents district boundaries in a portion of a country in Carto. The polygon data is represented in a string column of Well-Known Text lat/long vertices, which I'm ...
1
vote
1answer
175 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
1answer
38 views

Error load new layer from query result

I tried many times to create new layer from query result, but i got this error message, when loading it. " PostgreSQL layer has no primary key." SELECT * FROM pgr_drivingDistance('select gid::...
0
votes
0answers
69 views

Strange PostGIS errors after having altered its extension by specifying the postgis schema

Context I'm trying to refine a PostgreSQL (v.10.12) trigger in order to actually execute some computations on point features only if the input points do intersect a convex hull stored in an other ...
0
votes
1answer
87 views

Problem with clob data type when running ogr2ogr command

Using ogr2ogr, I transferred data from the oracle database to the postgres database. However, the table "table" contains attributes that have a "clob" as a data type, which were not created. Is there ...
-1
votes
2answers
39 views

PostgreSQL database designer that supports PostGIS geometry data types

Does anyone know if there are PostgreSQL database modeling software or apps that support PostGIS geometry data types? I know pgModeler is supposed to but I have been unable to get this installed.
0
votes
1answer
549 views

PostgreSQL Query which returns geom column as WKT set in function

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[...
12
votes
5answers
4k views

Creating Vector MBTiles?

I am searching for a tool to generate vector-tiles that I can then display using the Mapbox Map-View. We currently have a postgres database with all of our data. What is the best tool to generate ...
4
votes
1answer
79 views

Count number of points outside of Buffer?

I'm trying to count the total points outside of a buffer zone using PostgreSQL. The intention is to find all of the schools that do not fall within a 2 mile radius to libraries. I've either gotten ...
12
votes
5answers
17k views

I have not found shp2pgsql in postgis installation

I have been installed postgis following this guide. All is OK. SELECT postgis_full_version(); "POSTGIS="2.1.7 r13414" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, ...
3
votes
1answer
252 views

Unable to read TIFF file with raster2pgsql

I am having some trouble with the raster2pgsql function (on a Windows 7 in the OSGeo4W shell). I am receiving the error "Unable to read raster file". I can import shapefiles, ok using org2org so I ...
2
votes
2answers
126 views

Results not showing in SQL window in DB Manager QGIS 3.12

The following query executes in 0.149 seconds but the results will not display in the box in the bottom half of the DB Manager window, which I assume is the results window (PgAdmin returns 7 rows with ...
0
votes
1answer
50 views

Alternate of intersects ?# operator in postgres

Is there any way to find if a path is intersecting with a box in postgres without using ?# or using an alternative? I have this query below and hibernate's EntityManager is throwing an exception ...
1
vote
0answers
45 views

PostGIS query to retrieve geometry that intersects both a bbox intersects and also matches other columns

I am relatively new to GIS, so have been searching around to try find a similar case to the one I am facing but have come up short. Specifically I want to store multiple sets of geometry features in ...
2
votes
1answer
115 views

Speed up the shortest distance calculation using pgRouting + PostGIS

I have to calculate the shortest path and corresponding distances for 300k+ origin-destination (OD) pairs. Using the query below it takes me around 0.3 sec to calculate one distance and therefore more ...
0
votes
0answers
66 views

Selection on PostGIS layers is… randomly working in QGIS

Context Quick question... Since the last minor update of QGIS 3, I cannot select features anymore on some (not all) PostGIS layers (see Fig.1). Fig.1: On the left table, the selection is not ...
0
votes
1answer
388 views

Select all cities in Germany from city and country layers?

I have 2 layers: "layer_1": countries of Europe "layer_2": cities of Europe I want to create (and then save) a "layer_3" which would be all the cities of Germany. Is there any good tutorial of how ...
1
vote
1answer
64 views

Update PostgreSQL table; insert data from subquery, on conflict do update duplicate id

Context I run into (some good old) troubles when trying to update a PostgreSQL (10.12) table called nodes containing nodes with some few attributes from OSM + some few others. I only want to update ...

1 2 3
4
5
67