Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
3
votes
3answers
191 views

Select polygons within polygons in same layer

I have a buildings layer that actually has a flaw. The inner courtyards, drawn as another polygon within the building polygon, should be islands of the building polygons. Instead, they are other ...
0
votes
1answer
24 views

Division of polygon into irregular shape and varying area of polygons

I have a major polygon and with its area, I want to divide this polygon into any numbers of sub polygon. I have also area of sub polygon and number into which major polygon is divided. I want to ...
2
votes
0answers
19 views

MapServer / PostgreSQL don't display an ITEMQUERY

I try to search a string in MapServer with the method ITEMQUERY from a PostgreSQL table,. It look very simple but I receive the folow error: msPostGISLayerWhichShapes(): Query error. Error executing ...
1
vote
1answer
53 views

Storing Google Maps API returned polyline in PostgreSQL (or PostGIS)

By making Google Maps API call, I received a response JSON which contained this polyline: ezdvBcps}Lt@W@F@BVKl@UK]Wc@}@kAOKBEzA{BdA}Ab@s@LBf@RjARZBv@?rAAfBIX?f@FLBf@^b@hATbAF~@BvB?hALjBHpCLjAVdAd@...
0
votes
0answers
14 views

Serial auto-increment when insert table into another table [closed]

When I insert a table (station) into another table (postes), the auto-incrementing serial "id_hta" is lost. INSERT INTO station (code_hta, geom_hta) SELECT code, geom FROM postes; So, I ...
4
votes
1answer
27 views

Update array geometry field using array lat & long field in PostGIS

I have two array columns in Postgres named lat and long. Both have 1-5 values in their array field. Like the following Now I need to create geometry from each value based on the array index of lat &...
4
votes
1answer
48 views

How to reformat SQL statement to export GeoJSON from PostGIS table?

So, I realize this question gets asked a lot, but the root of my question is related to the syntax of the SQL statement used to format the result table as GeoJSON. I am currently able to execute a ...
1
vote
0answers
33 views

Foreign Data Wrappers

I had created a foreign server in the E drive with a data source of "e:/files/" for Map info tab files. It was all working well until I had to refresh or re-create the server. It gave an &...
0
votes
0answers
9 views

Issue with query planner in postgres [migrated]

I have this query which has suddenly turned really really slow select * from satelliteproduct where supplier_id = 226 and supplierproduct_id IN ('123', '234'); If I query an explain analyze I can ...
2
votes
1answer
40 views

Cannot load WFS via QGIS into PostGIS

I try to load this WFS https://fbinter.stadt-berlin.de/fb/wfs/data/senstadt/s_lor_plr_2021 into PostGIS using QGIS. However, I always get the following error Error: 6 Object writing error: Object ...
4
votes
1answer
56 views

Gaps and Islands problem in PostGIS for GPS traces

I'm using ST_MakeLine to create traces of ship movements. The query below yields all the GPS points on a certain day, and I'm using ST_Within to only find ships in the navigable portions of the river. ...
4
votes
1answer
92 views

Shortest paths between points over multiple linestrings

I'd like to calculate the distance between one and multiple other points along a network of LineStrings. I'm aware of this question which calculates the distance on a substring of a LineString, but ...
1
vote
0answers
27 views

Left join on Postgresql and QGIS returning different results?

I'm using the following expression to join to layers and produce a virtual layer. select * from table1 left join table2 on table1.field1=table2.field2 When I do it in Qgis, my virtual layer doesn't ...
0
votes
0answers
16 views

Loading PostgreSQL table to QGIS using Psycopg2 and PyQGIS

I am creating a plugin that allows the user to export multiple tables from a PostgreSQL database to QGIS, for this I am using psycopg2. So far, my code allows me to: Connect to the database. Get the ...
2
votes
1answer
21 views

QGIS n to n relations

I have one table called "sites" and another table with bibliographical references called "biblio". Each records in "sites" could be related to multiple records from "...
0
votes
0answers
18 views

QGIS: Populate a QTableWidget in a custom-ui-form by PostgreSQL data

I want to query Objects from a QGIS-Layer with the Object-Select-Tool. The Data should be displayed in a custom gui form "dvl_flaecheninfo_v01.ui", i.a. in a 1:n-connectet QtableWidget like ...
-1
votes
1answer
68 views

PostgreSQL/PostGIS database in OneDrive?

Is it possible to install a PostgreSQL/PostGIS database in an OneDrive-Cloud and access it with QGIS?
7
votes
2answers
89 views

Plot a point at the boundary of all polygons facing the nearest road line in PostGIS

I want to add/plot a point geometry at the boundary of all home polygons. Conditions: The point should be facing the road geometry. As it will considered as the approximate entry point for that ...
0
votes
0answers
15 views

Import only Point features with osm2pgsql | Import only certain Tags of point features with osm2pgsql

I am very new osm2pqsgl. I have downloaded a osm.pbf file for all of Europe and I want to add this data to my Postgres database. However, I am only interested in points, no linestrings nor polygon, ...
6
votes
2answers
72 views

Making multipoint column with at least 2 points

I have a table called my_table that has a MultiPoint column. I want to limit the MultiPoint column. A Multipoint must have 3 Points most and it must have 2 points at least. How can I do the limitation ...
2
votes
1answer
25 views

PostGIS equivalent of Pandas geometry.area for square feet

I have a Python script that has a line that takes the geometry and calculates it into a square feet value by multiplying the area by 10.7639 in a geopandas dataframe. bldg_feat_poly_df['bldg_sqft'] = ...
1
vote
0answers
35 views

How to change the geometery type multilinestring to polygon in a JSON file through og2ogr [closed]

I have have a JSON file of earthquake data. The data type within the JSON file is a MULTILINESTRING. For my further workflow I need the data type POLYGON. I am able to import the dataset as POLYGON ...
4
votes
1answer
65 views

Joining 3 tables (Points, lines and Polygon) in SQL

I am trying to ask two questions in one query using SQL. I want to know: a) which polygon (ward) a line (road) is in. and then b) within that ward, which points (B&B's) are within 1 mile from the ...
2
votes
1answer
52 views

How does ST_ClusterWitihin and ST_ClusterDBSCAN scale?

I have a table of US mailing addresses with approximately 5 million rows. I want to form clusters of addresses located within about 10km of each other. I have a GIST index on my geometry point column ...
10
votes
3answers
269 views

Creating polygon around set of points without blank polygon spaces in between in PostGIS

I want to create a polygon around a set of points which should exactly just contain the set of points without leaving any blank spaces in between. Below is the best output I am getting after using ...
0
votes
0answers
11 views

Enable Archiving and Enable Editor Tracking on Oracle SDO

Are enable archiving and enable editor tracking allowed in Oracle database system (SDO)? Since I usually use PostgreSQL, I don't know the features supported by the Oracle database.
0
votes
0answers
41 views

How to search street and housenumber

I installed OSM maps on POSTGRESQL database I installed the osm.pbf file via Osm2pgsql. after that i created a project in QGIS 3.16 Software. This installation is for offline use. I'm unable to ...
3
votes
2answers
81 views

ST_Intersection fills up LIMIT with empty geometry points

I have managed to get the intersection between a circle around a point and objects in a geometry using this PostGIS query: SELECT ST_Intersection(sea_level.geom, ST_Buffer(ST_Transform(ST_SetSRID(...
2
votes
1answer
48 views

Avoiding this PostGIS error: "Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY"

I have: a table crashes with data on car crashes in nyc a table locations with data on the locations of NYC schools Both have latitude and longitude columns. I'm trying to generate a table with one ...
0
votes
1answer
45 views

Why is the Export to PostgreSQL function in QGIS to a remote database extremely slow?

I posted this on Twitter but I'll ask here as well - I need to load spatial data into a remote PostgreSQL database (i.e. a db on a VPS in the cloud or hosted SAAS db). When I use the Export to ...
1
vote
1answer
26 views

Getting GeoServer primary key metadata table working [closed]

I am having trouble getting a primary key field to be successfully accepted in GeoServer for spatial views (in PostgreSQL), with and without forcing it using the gt_pk_metadata table. I am seeking ...
2
votes
1answer
32 views

Dynamically formatting SQL string in Python using pscopg2 for use in geopandas.from_postgis method

I am trying to develop a relatively generic function in Python that will filter any PostGIS table in a database using an arbitrary mask (a GeoPandas GeoDataFrame) and the ST_Intersects argument, but I ...
0
votes
0answers
23 views

How to define turn restrictions in pgrouting

I try to make navigation system for calculating the shortest path in Postgres via pgRouting. In my dataset I have roads with all needed attributes (road ID, name, length, oneway…). I have one more ...
0
votes
1answer
64 views

Installing PostGIS extension to PostgreSQL with Stack Builder

I am trying to add the PostGIS extension to PostgreSQL. My efforts to add it to my existing Postgres application failed, so I uninstalled and reinstalled it. I used the EDB installer. Everything went ...
1
vote
2answers
39 views

Habitat area within UTM grid in PostGIS

I'm using PostgreSQL 14.1 with PostGIS 3.1 and I've got two tables malla, that contains a UTM grid of 10x10 km, and CLC18, that contains the habitat polygons of the Corine Land Cover project. Now I ...
1
vote
0answers
37 views

Difference between PostgreSQL and PostGIS when working with GEO data

I'm debating installing the PostGIS extension for PostgreSQL. However, upon looking at the types in PostgreSQL itself, it seems to support the basic Geometric types, including: 8.8.1. Points 8.8.2. ...
0
votes
0answers
28 views

Raster not loading to PostgreSQL database

I am trying to load rasters to my PostgreSQL database with PostGIS enabled. I am following the tutorial from here for using raster2pgsql: https://postgis.net/docs/using_raster_dataman.html#...
1
vote
1answer
34 views

Select polygons that are within other polygons and have a certain area in PostGIS

I'm using PostgreSQL 14.1 with PostGIS 3.1 and I've got two tables malla_p and pen_bal. I want to select those polygons of table malla_p smaller than 10,000 ha that are within the polygons of table ...
4
votes
3answers
55 views

Returning count of all points within specified range to single point using PostGIS

This may be more of a SQL fundamentals question but I'm sure somebody on here has encountered this before. I have a database of geospacial data (around 4 million points) using PostgreSQL/PostGIS I ...
1
vote
0answers
27 views

Sending vector tile PBF content as response from HTTP Azure function

I wrote Azure function (based on HTTP trigger) and need to return vector tile PBF as response. I used following piece of C# code to send response from Azure function return new FileContentResult(...
3
votes
1answer
38 views

PostGIS generate center POINTS from multilinestrings and multipolygons (for each line and polygon) in the memory

Let's assume, I have a mixed geometry type table in PostgreSQL The multipart geometries don't intersect themselves. I would like to generate a center point collection from every geometry. I know how ...
1
vote
0answers
17 views

Exporting metadata from PostGIS tables to shapefile using QGIS

With QGIS, the description of a dataset seems to be available for filling and consultation via the 'information' and 'metadata' tabs in "LAYER PROPERTIES". There seem to be bridges between ...
0
votes
0answers
18 views

QGIS/PostGIS : Disable double authentication request

I work on Windows with QGIS 3.16.6 and PostgreSQL/PostGIS. I have shared a QGIS project to several users. The layers are PostGIS tables. For some time, whenever an user opens the project, it has been ...
1
vote
0answers
33 views

PostgreSQL API and QField

Does anyone know how to create a QField API, because I think it's going to work in my case. What I want to achieve is load a heavy PostgreSQL DB on QField (The project contains a lot of tables about ...
0
votes
1answer
34 views

Max of count does not give me the result I need

I need to display the max of count for this query. I tried a nested way but I could not get it to return the centroid of that cell. Here flickr_edin table data consists of points and grid100m table ...
2
votes
1answer
28 views

Query executed but returns blank screen

I have two tables, flickr_edin and routea flickr_edin contains columns id, date_taken, user and geom. routea consists of a single entry of a line string under the geom column. Use flickr table to show ...
2
votes
2answers
31 views

Operation on mixed SRID geometries error

I have two tables flickr_edin and temmpat. temppat was created using: CREATE TABLE temppat (id serial, geom geometry); INSERT INTO temppat (geom) VALUES (ST_GeomFromText('POINT (326253.33 674110.63)'))...
3
votes
1answer
61 views

How to store historical value of a raster pixel in postgis raster datatype?

I have a postgres table to store rasters having elevation value in each pixels. the raster size on average is around 100x100 with scale of 1. The elevation value is historic and I store timestamp in ...
0
votes
1answer
29 views

Geometry simplification taking time

I'm doing using an SQL query to simplify the geometries and transform them into GeoJSON, and later use it in Leaflet. However, I am facing a small problem: queries that return the geometries of cities,...
0
votes
1answer
36 views

How to increase tilesize for OSM database

I'm trying to create a custom tileserver with express and Postgres. I've imported the OSM planet.pbf file into a Postgres database with PostGIS installed and I can currently serve tiles in 256x256, ...

1
2 3 4 5
76