Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
2answers
23 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
24 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 ...
3
votes
2answers
21 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
0answers
12 views

Secure SQL update in PHP [migrated]

As part of a job I want to update a database using a form. Since the database is large and is used by many users, I hope that this manipulation is at least secure for more safety. HTML script : <...
1
vote
0answers
12 views

Linking new feature violates foreign check constraint in postgres 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
0answers
9 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 ...
1
vote
1answer
96 views

Updating PostgreSQL database with PHP language

I want to update the text information contained in my database for a specific layer. This layer is called via a WFS stream. At first I displayed my layer with an Overlay with OpenLayers, and with a ...
3
votes
1answer
49 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
10 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
0answers
33 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 ...
1
vote
1answer
25 views

Edit array type attributes in attribute table

I loaded a PostgreSQL layer in QGIS project. The layer has some array type attributes like integer[]. I'd like to edit these attributes in Attribute Table. But it doesn't work, I could not type any ...
2
votes
0answers
39 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 ...
-1
votes
1answer
31 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
18 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 ...
0
votes
1answer
19 views

<ServerException>Layer(s) not valid</ServerException>

I am using QGIS Desktop 3.12.3 and QGIS Server and I have the following issue: a WMS generated based on a set of data stored in a Postgres db returns on GetCapabilities: <ServerException>Layer(s)...
2
votes
2answers
35 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 ...
0
votes
1answer
26 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....
1
vote
1answer
36 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 ...
1
vote
1answer
17 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 ...
0
votes
0answers
51 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 ...
0
votes
0answers
65 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 ...
1
vote
1answer
37 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
2answers
29 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.
4
votes
1answer
76 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 ...
0
votes
1answer
29 views

What is the geometry type if I am having mixed geometry?

I have a mixed geometry data some belong to MULTIPOLYGON and others belong to MUTIPOLYGONZM and I want to insert the data into one
2
votes
1answer
17 views

Postgres: Remove Duplicate Rows returned by ST_Dwithin query

I am having trouble finding a query that returns one row for every unique vehicle crash that is within 100 feet of a select group of road features. My query currently returns a row for every collision ...
0
votes
0answers
65 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
11 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
25 views

ArcGIS client user connection hanging when connecting to PostgreSQL geodatabase

ArcGIS Catalog and Pro are both hanging when I try to connect as a user that is a member of a role. I think it is related to the SDE schema permissions but I'm not sure. I have an enterprise ...
1
vote
1answer
56 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 ...
0
votes
0answers
10 views

How to install pgRouting and use it in pgadmin4 in OSX

Is there a straightforward way to install pgRouting in my existing PostgreSQL(version 11) installation and use it through my pgadmin4 by creating an extension? I am using OSX.
1
vote
2answers
57 views

PostGIS returns an empty GeometryCollection out of an intersection between a MultiPolygon and a Point

Context I've narrowed down an issue I faced involving many buffers and so on, to this in PostGIS (version 2.4): SELECT St_geometrytype( St_geomfromtext( 'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0), ...
1
vote
0answers
44 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 ...
0
votes
1answer
42 views

PostgreSQL function return list of geometry

I have a query that return result as expected - list of points. I would like to create a function with two parameters: input polygon geometry N as number of points to create WITH polygon AS (...
2
votes
1answer
33 views

How generate ID uniquely in two featureStores with GeoTools?

I have a PostGIS datastore in GeoTools as follow: JDBCDataStore dataStore = ... and I have 2 tables(or FeatureStore in GeoTools) as follow: SimpleFeatureStore table1Store = (SimpleFeatureStore) ...
1
vote
1answer
29 views

ST_Transform not working SRID

I have some MultiPolygon geometries in SRID 4258 and am trying to transform them into SRID 4326. When I run the following command, however, the output still seems to be in SRID 4258. Command: SELECT ...
0
votes
1answer
15 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 ...
1
vote
1answer
35 views

How to group by with sum and aggregation? [closed]

I would like to organise a table with some conditions. I need to order by id, and to add a column that make sum of the same species, with the same id (cf.bold charachter). Do you have any idea ?
2
votes
2answers
42 views

Changing database connection parameters in QGIS projects stored in PostgreSQL

We have stored our QGIS projects in a PostgreSQL database. Now we have to move the server that hosts the database that many projects access. Is there a way to change the connection parameters within ...
0
votes
1answer
26 views

Find the points within radius using PostgreSQL

In my table I have three columns ID , long and lat how to find points within radius (ex., 20 km with center long and lat) without using PostGIS.
3
votes
1answer
29 views

INSERT only non-existing features into PostgreSQL/PostGIS table with FME

I have a PostGIS table of, lets say, adresses, for example id | adr | geom ---------------------------- 1 | Rathausplatz 1 | POINT 2 | Dorfstraße 2 | POINT 3 | Kirchenweg 3 | POINT ...
0
votes
1answer
36 views

How to use ST_Distance (Closest polygon to point)

Can I know how to interpret this SELECT statement for PostGIS? I am unsure where do I insert my own coordinates in this statement. SELECT geom FROM polys, points WHERE points.gid=1 AND ST_DWithin(...
0
votes
1answer
22 views

Get all rows that have geometries which intersect with a geometry in any row of a different table in an effective way

I have two PostGIS tables containing thousands of geographic polygons each (each row has one polygon and numerous other attributes). Both tables represent different data sets but cover the same area ...
0
votes
0answers
20 views

Missing gml_id when using ogr2ogr from WFS to PostGIS / GPKG / GML

I am trying to find the way to download on our PostgreSQL+PostGIS database the content of several WFS at regular intervals. It has to be done at regular intervals so we want to the whole thing to be ...
1
vote
1answer
43 views

QGIS Polygon Shapefile to PostgreSQL (PostGIS)

I am working on getting the polygon data of different areas loaded into PostgreSQL so that I can do searches within those areas based on address points. I have downloaded postcode data from: https://...
0
votes
1answer
34 views

How to update tables from QGIS to pgAdmin?

I have created a connection between QGIS (ver. 3.10.1-A Coruña) and PostgreSQL (pgAdmin 4). Every time I edit/update my table in QGIS I have to delete the table in PostgreSQL and re-import again the ...
0
votes
1answer
45 views

Which files format should I favour for import into PostgreSQL+PostGIS? [closed]

Although not a newbie, I am not a GIS expert, so I can use your help. I have this GIS project, based on a PostgreSQL+PostGIS database. I import GIS data from many sources. Most of them offer several ...
0
votes
0answers
43 views

How to modify the columns to geometric points

How make points geometric if already exist columns with data like column names are gps_latitued and gps_longititude in mapping table
0
votes
1answer
18 views

PGRouting Error Missing table?

I am trying to use pgrouting to find the shortest path in GeoServer. So I created new layer in sql view but its giving me error. I created 3 table in database: at_2po_4pgr = main table ...
1
vote
1answer
43 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 ...

1
2 3 4 5
66