Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
1
vote
0answers
16 views

How to fix an error while registering PostgreSQL database to ArcGIS Enterprise Server

Software in use is: ArcMap 10.8.1 ArcGIS Server (Enterprise) 10.8 PostgreSQL 13 + PostGIS 3.1 Windows 10 64bit I followed a guide to register a PostgreSQL database (PostGIS) to ArcGIS Server. When I ...
0
votes
0answers
61 views

What is the best way to analyze a very large csv file of Geospatial data? [closed]

What is the best way to analyze a very large CSV file of Geospatial data (5-6 GB) containing lat-long coordinates?
2
votes
1answer
27 views

PostGIS: How to join the original polygon name to Voronoi Polygons?

I am trying to replicate this study using the counties (comarques) in my country. https://tjukanov.org/closest-to-me Thus, I'm trying to find which is the closest neighbouring county to any point in ...
1
vote
1answer
33 views

Is the geometry type of PostGIS represented as WKT or just the Point type?

Despite reading a lot of materials on the subject, including the PostGIS manual I still haven't been able to understand, I'm a beginner in this world of geographic objects. I have two tables in my ...
2
votes
1answer
15 views

How to group insert statments into transactions when using ogr PGDump driver?

I tried the -gt option to group insert statements into transactions, but apparently it doesn't make a difference: ogr2ogr -f PGDump test1.sql test_6350.gpkg -lco SRID=6350 -lco DIM=2 -nlt ...
0
votes
0answers
27 views

configure osm2pgrouting xml for bicycle routing

I need to configure osm2pgrouting mapconfig.xml to import all ciclyng roads. I already read this post: https://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/discussion/topic/391.html that ...
2
votes
1answer
54 views

Invalid SRID return from PostGIS ST_Centroid Function

I'd like to get the centroid of a set of polygons with SRID 4326 I start from this simple query to verify the ST_Centroid function CREATE TABLE test AS SELECT ogc_fid, geom, ST_Centroid(geom) ...
2
votes
1answer
35 views

saveStyleToDatabase unable to save using PyQGIS

I have been trying to programmatically save the styles of a number of layers to my postgres database using the following code: for layer in QgsProject.instance().mapLayers().values(): lyr_name = ...
0
votes
0answers
21 views

QGIS - error connection of database in RiverGIS

Currently, I am facing a problem to connect Postgres database to RiverGIS. It shows this error. Couldn't connect to river database: rivergis@localhost Please, check you database connection settings! ...
1
vote
0answers
38 views

PostGIS data storage suggestion for lines with multi-attribute points - Single huge POINTS table or LINES table with geometry field(s)? [closed]

I am currently working my way through PostgreSQL with PostGIS, trying to figure out the optimal way to handle the storage and manipulation of a very large dataset. I have read this and this question ...
0
votes
0answers
22 views

Users can't save styles on the db PostGIS

I have a PostgreSQL database, and only with the PostgreSQL user (who's the owner of that table), I'm able to save styles to the database. I checked the user's permissions for the other group roles and ...
0
votes
0answers
22 views

Migrate MySQL spatial DB/tables to PostgreSQL

We have a web system that uses MySQL spatial databases. We want to migrate from MySQL to PostgreSQL. I've been looking for methods to do it. I have found some tools that help you to migrate. However ...
4
votes
2answers
310 views

Closest point on 5km line

Given a point and a line, I am trying to determine the point on the line that is closest to the given point. The given point is always at least fairly close to the line (3-4 meters max) Originally I ...
1
vote
1answer
31 views

ogr2ogr shapefile to PostgreSQL

Trying to use ogr2ogr to import a shapefile in PostgreSQL. Here is my command: cd C:\Program Files (x86)\FWTools2.4.7\bin &&SET PGCLIENTENCODING= Windows-1256 &&ogr2ogr -f "...
0
votes
0answers
14 views

Greenplum/PostgreSQL - Not Able to Access Fields Retrieved in Subquery [closed]

The following code works just fine: select t1.*, case when x = 10 then 1 when x = 20 then 2 else 0 end as value from table1 as t1 left join table2 as t2 on t1.status = t2.status but ...
1
vote
1answer
53 views

PostGIS: How to return polygon nearest the point

I have two tables in a PostgreSQL / PostGIS database 1 polygon and the other point data How do I modify the following query so it returns only the smallest polygon nearest the point The red in the ...
4
votes
1answer
37 views

pgr_dijkstra is considering one-way streets as bidirectional

I imported data from a map extracted from OpenStreetMap. With osm2pgrouting I imported it into PostgreSQL, to be worked with pgRouting and PostGIS. Command used: osm2pgrouting -f DIRECTORY/map.osm -d ...
1
vote
0answers
18 views

Install PostGIS with MacOS Big Sur

I am totally new to either PostGIS or PostgreSQL. I am trying to install PostGIS on my computer, which is running with MacOS Big Sur version 11.2.3 I have installed PostgreSQL 13 with the EDB. Then I ...
0
votes
0answers
26 views

Generate MVT with POSTGIS and feature tolerance

I have a database which has three geometry tables 'tbl_point, tbl_line, tbl_polygon' with millions of features. Each table has two geometry columns named geom & m_geom for geometries and multi-...
0
votes
0answers
13 views

Registered Spatial View ObjectID field error

I am creating a spatial view in postgresql (geodatabase) and adding auto incrementing objectid field. I tried the following two ways to create it but in both cases once I register the view to the ...
0
votes
0answers
17 views

(Auto)Convertion from point-coordinates (postgres) [duplicate]

When loading data from my postgres-db (where I can only read from, so not my own db) the field with the point-coordinates like .. 'POINT(390002.1610814564 6001433.406049595)' .. are getting (auto)...
0
votes
0answers
27 views

raster2pgsql command line issue (Windows 10) - ERROR: syntax error at or near “raster2pgsql”

I'm using the OsGeo4W64 shell that comes with QGIS (I'm using Windows 10) and have managed to log into my postgres database. However I'm looking to import a raster into a new table, and all roads seem ...
1
vote
1answer
53 views

How to open QGIS project without being asked for PostGIS credentials every time

Problem: We have several layers stored in a PostgreSQL database with PostGIS extension (Stored on AWS RDS Instance). The connection has been working perfectly to date. I get asked for login ...
4
votes
2answers
115 views

Geometry vs geography vs spheroid for polygon-contains-point (PostgreSQL)

Many operations behave differently in geometry vs geography, e.g. the distance between two points. But what if I only need to check whether a polygon contains a point? Does that operation differ ...
1
vote
1answer
60 views

PostGIS VS Overpass: is it possible to get this PostGIS query faster? Query mistake?

I'm trying to get all the road data within a boundary and to load this data into R. I compared Overpass vs PostGIS and PostGIS at least for me was a magnitude slower. It is 116.322 sec vs. 7.937 sec : ...
3
votes
1answer
40 views

How to get in point geometry to pinpoint accurately where geometries have mistakes?

Basically I have some line and some polygon tables which have invalid some geometries and apparently PostgreSQL returns errors when trying to return reports which rely on intersecting these features ...
2
votes
1answer
59 views

Create a subset of geometries which intersects with each other

I have a set of geometry in my PostgreSQL table that consists of Points, Linestrings & Polygons. From this set I need to create a subset of geometries which intersects with each other. I need a ...
0
votes
1answer
26 views

Show sensor node location in QGIS MapCanvas

I have a Postgres db storaging data from a mobile sensor node, and want to show the location in a QGIS MapCanvas. Data is in a column in a jsonb format (Location and temperature). I'm trying to use a ...
3
votes
3answers
42 views

Generating a GeoJSON MultiLineString Feature with multiple lines from a GROUP BY clause

I'm trying to create some GeoJSON directly from PostGIS (v3.1; Postgres 13.2) and have gotten surprisingly far, considering my aversion to raw SQL queries. Specifically, I want to create a Feature ...
2
votes
2answers
187 views

Generate a geometry column from latitude and longitude columns with SQL

My table has the columns latitude and longitude in float8 type. I want to generate a geometry column from these two columns. I make the following query in the SQL Window of the DB Manager in QGIS. ...
1
vote
1answer
24 views

Mixed SRID's when using users Lat & Lon coordinates to perform KNN through PostGIS when building points to jsonp

I have managed to create a script that passes the users lat and lon to a postgres query that finds the nearest 5 pubs to a users location. However, on execution I realised no matter what lat and long ...
1
vote
1answer
41 views

PostGIS select points where they intersect a polygon, but on some attribute values, apply buffer prior to intersect

I want to select some points based on a intersection with a polygon coming from an other database as a WKT geometry. For the moment, everything is working fine: SELECT p.name, p.geom, p.class FROM ...
1
vote
1answer
42 views

Import multiple GML files into PostgreSQL 11

I have some 50 GML files in my downloads folder, each within a child folder such as: Downloads\Adur\Land_Registry_Cadastral_Parcels.gml Downloads\Abertawe_-_Swansea\Land_Registry_Cadastral_Parcels.gml ...
0
votes
0answers
17 views

Technology moves on but this is still a relevant Question How to embed a map into Access Database? [duplicate]

I'm currently trying to build a front end interface for a land-parcel management system in Microsoft Access and in displaying individual parcels with their attribute data - I wanted to display on the ...
2
votes
1answer
52 views

Create linestring from coordinate values in text array after rounding to 3 decimal points

I have a table like; Id coordinates (text[]) 1 {"609275.485000001 4253109.911000001","609275.257 4253109.641000001"} 2 {"617784.171 4268033.896000001","617783.868 ...
1
vote
0answers
31 views

Hiding / encrypting password in QGIS processing script

Is it possible to hide or encrypt password in QGIS processing script? For example, hide password for PostgreSQL connection. QGIS "Master password" is possible, but I need a solution for code ...
2
votes
0answers
23 views

Connection pooling in OGR PostgreSQL driver

OGR (GDAL) provides a vector driver for "PostgreSQL" and data in the DB can be accessed with something like the following: driver = ogr.GetDriverByName("PostgreSQL") datasource = ...
2
votes
3answers
157 views

How to overlay layers with different SRID using QGIS and PostGIS? [closed]

I have a map of the Netherlands imported from a SHP-file into my PostGIS environment with SRID 4289 (Rijksdriehoek Amersfoort). When I connect using QGIS to the PostGIS database and add the layer with ...
1
vote
0answers
32 views

PostgreSQL cannot work when opens SSL on Windows 10 [closed]

I am not sure this topic will fit here but I don't know ask it where. I am trying to open the SSL of PostgreSQL 10.16 on Windows10. I read a lot of documents about creating an SSL certificate to ...
2
votes
1answer
56 views

Using st_buffer to generate buffer away from edge of polygon

I am trying to find which bike paths come into an area no closer than 500m but no further than 700m of Melbourne. I believe I will use an st_buffer to create a buffer around Melbourne (I have a ...
0
votes
1answer
24 views

What is the fastest way to create a polygon of the part of PostGIS raster that is not filled with NoData values?

I have a raster table in PostGIS. For each row, I want to create a polygon that represents the part of the raster that is filled with data (not NODATA). I found out that ST_Polygon(rast) does the job. ...
0
votes
0answers
23 views

How to return only lines that intersect for a certain length instead of just

Basically I have 2 line layers that intersect. If I run: SELECT objectid, object_class FROM table1 d WHERE st_intersects(d.geom, (SELECT geometry FROM table2 WHERE id = 75259) ) I get at least 10 ...
1
vote
1answer
36 views

Does Qfield include SSL?

I am a beginner in this field. I am working with PostgreSQL, Qgis, and Q-field to collect data on the field. As you know, I am searching for methods to keep security my database when I open a firewall ...
1
vote
1answer
19 views

pgr_dijkstra with dynamic IDs

I am trying to use dynamic IDs so I can create a bounding box over the edges. I do have a starting road, which I set up before the query. The closest road_id from a city's coordinates. (i.e. 923342 in ...
1
vote
1answer
71 views

PostGIS: How to find Nearest Neighbour but only within a direction range?

I am attempting to find the nearest neighbour to a geometry in a certain direction but am becoming stuck at the planning stage. I understand the basic tools that I need but am failing to find anything ...
0
votes
0answers
121 views

gdalinfo: /usr/pgsql-13/lib/libpq.so.5: no version information available (required by /lib64/libgdal.so.27)

After the installation of postgis 3.1 and postgres 13, GDAL is complaining about libpq.so.5 library: gdalinfo: /usr/pgsql-13/lib/libpq.so.5: no version information available (required by /lib64/...
0
votes
0answers
63 views

Upload TIFF files to PostGIS

New to the GIS, I want to upload these file formats to PostgreSQL server .tfw .tif .tif.aux.xml tif.vat.cpg tif.vat.dbf tif.xml tif.ovr I used the standard org2ogr way to upload files but it wouldn’t ...
0
votes
1answer
26 views

ogr2ogr spatial query doesn't work in process

I tried exporting data selecting it from postgres with spatial query. The argument does work within osGeo4W Shell but I'm trying to use it from my C# console app. Everything seems too work until I add ...
0
votes
0answers
36 views

How to connect Qfield to PostgreSQL by online?

I created a database on PostgreSQL, connect it to QGIS through PostGIS. Next, I package for the QGIS project and send it to my mobile. But it gets an error, my phone cannot read the database, it calls ...
1
vote
1answer
18 views

osm2po missing ways for Romania

I've been trying to import all the ways for Romania into my PostgreSQL database using Geofabrik and osm2po. However, I noticed that some chunks of a county road are missing by using the following ...

1
2 3 4 5
72