Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

2
votes
0answers
17 views

QGIS Server Plugin Loading Problem: 'unexpected type 'sip.wrappertype'

I'm using 3.6 qgis on the Ubuntu 18.04 server. I made a plugin for limiting requests on our WMS server. I'm having troubles loading the plugin I get always error: Traceback (most recent call ...
1
vote
2answers
40 views

PostGIS line in polygon performance (ST_Intersects)

I am running an analysis to find lines that is covered by a polygon. I have to create the polygon from a road network (hence the buffer and union) because the the lines are not exactly equal. The ...
2
votes
0answers
54 views

Generalizing / simplifying multipolygon in PostGIS without missing polygons?

By following the rules from this site, I got the output but some polygons do not come within the simplified polygon. I think the problem is with topology. Is there any solution?
2
votes
0answers
32 views

QGIS 3.4 - Credentials to PostGIS connection constantly requested when panning the map

I am using PostGIS databases linked to QGIS 3.4 with credentials set for all users, in other words user-authenticated workspaces. Note: The credentials are not stored within the project file or setup ...
2
votes
1answer
28 views

Load PostGIS Layer with BLOB and use the form widget for binary fields (QGIS 3.6) not working

I have tried to load a PostGIS layer containing a binary data field (BLOB, bytea) and then use the new form widget for binary fields (https://qgis.org/en/site/forusers/visualchangelog36/index.html#...
1
vote
1answer
45 views

PostGIS: Running multiple intersections within a single SQL query?

PostgreSQL 11 + PostGIS, on Windows 10. I'm working with a PostGIS-enabled database. I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the ...
3
votes
2answers
93 views

Re-order multipart polygons in PostGIS

I have a table with multpart polygon geometry with coordinate system EPSG:4326. I can identify, what are geometries with more than 1 polygons in it. I want to re-order it in a way that the largest ...
0
votes
0answers
19 views

pgRouting TSP with eventual order constraints?

Is-it possible to run Travelling Sales Person (TSP) in pgRouting (I've got 2.3... installed) with constraints? A general TSP algorythm or those I've identified in pgRounting solve the TSP problem ...
1
vote
0answers
42 views

How to create a function for getting a largest polygon from 2 polygons using case statement in PostgreSQL?

CREATE OR REPLACE FUNCTION public.largest_polygon1( geom1 geometry, geom2 geometry) RETURNS geometry LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ begin Return Case ...
1
vote
1answer
39 views

How to calculate extreme point (most north or east etc) of geography(polygon) type in PostGIS

I have a table which include geography(polygon) type. CREATE TABLE place ( name varchar(100), geom GEOMETRY(POLYGON, 4326) ); insert into place SELECT 'place1', ST_GeomFromText('POLYGON((-71....
1
vote
1answer
53 views

PostgreSQL - Creating DB, User privileges [closed]

I created a database named "wchiba" in postgres using : create database wchiba; Then I create a user : create a user saifeddine with password "0000"; Then I grant privileges : grant all on ...
0
votes
1answer
23 views

password authentication failed for user “ASUS”

I'm creating table in PostgreSQL using these commands: shp2pgsql -s 32632 watershed.shp watershed > watershed.sql psql -f watershed.sql cambridge psql -f watershed.sql cambridge But I had ...
1
vote
0answers
24 views

Using CSW for GeoServer PostGIS database

I am quite new to GeoServer and general OGC fundamentals. I have GeoServer set up and I set up a PostGIS data store which links to a database I have already been able to make successive calls using ...
2
votes
1answer
33 views

raster2pgsql: queries on imported raster are NULL

I loaded a tif-file of some soil data into postgres with: raster2pgsql -c -I -F -C -r -M -s 31467 -t 200x200 soil_file.tif gis.soil_map | psql -U user -d database -h localhost -p 5432 which worked ...
1
vote
1answer
74 views

Type geometry does not exist PostGIS [duplicate]

I am trying to add a geometry column to an existing table in PostgreSQL inside a plpgsql function using EXECUTE format('ALTER table %I add column geom geometry(Point, 102003)', target_table); Its ...
4
votes
0answers
70 views

Increasing PostGIS/PostgreSQL memory to avoid Out Of Memory (OOM) error?

This is a follow-up question to my earlier question about the memory management of some PostGIS/PostgreSQL functions: How do PostGIS functions handle buffers and external storage? As suggested by ...
1
vote
1answer
47 views

Sending geographic data from Postman?

I am trying to create a GIS app using Java, Spring and Hibernate. I send a JSON from Postman like this: { "pointName":"any location", "geom":"POINT(-71.060316 48.432044), 3857" } The JSON ...
0
votes
0answers
19 views

pgRouting plugin error

I have used osm2po to create routing data after load the data to database(postgresql) then from it to QGis.when using the pgrouting plugin the error appears: 2019-05-16T12:43:17 WARNING ...
0
votes
1answer
137 views

How to create a function for finding the largest of 2 polygons by area in PostGIS?

I tried my level in creating the function but it shows error while running. I have enclosed the code here : create function largest_poly (geom1, geom2) returns geometry as $largest_poly$ begin from ...
1
vote
1answer
69 views

Mapping land plots which contain two buildings

I need to show land plots in QGIS that have two buildings. My query only shows buildings which are placed on land plots. Create or Replace VIEW dvi_budivli_view AS Select "budivly".*, "dilyanki" ...
1
vote
2answers
58 views

shp2pgsql command to import shapefile [closed]

I have a problem loading my shapefile named watershed and link it to my table in postgresql named watershed as mentioned here: shp2pgsql -I -s 32632 C:\Users\ASUS\Desktop\master_PEER\DATABASE\...
0
votes
1answer
55 views

Citing PostGIS in formal publications?

I have worked a piece of academic work using the PostGIS extension on PostgreSQL (pgadmin). I have done a lot of work include buffer generation and data processing. How do I cite this software for ...
1
vote
1answer
44 views

Renaming duplicates with any vacant value within a certain interval

The values of my column of interest (col1) should range from 0-999. I'm looking for a way to assign one of each duplicate a new value that lies within my this range 0-999. Only features that are ...
2
votes
0answers
57 views

Split Line by points with PostGIS

I'm having some troubles trying to split a Linestring with 3 points. My line is a circular route from a Garage (GA) to a pick up (PU) point, then a drop off (DO) point, and return to garage (img1) ...
2
votes
1answer
61 views

Improving Mapnik render time

I'm adding polylines (elevation curves) to a world map. Said polylines are stored in a PostgreSQL database from gdal_contour tool. The geometry columns (LineString, 3857) has a GIST index and a ...
2
votes
0answers
24 views

Point cloud query is very slow and not using parrallel scan or more memory

The following PostGIS query is running very slow (> 1 day for about 1 million records): UPDATE test.table1 SET area= ST_Area(PC_EnvelopeGeometry(pa)) WHERE area is null or area <> ...
1
vote
0answers
24 views

Bounding box algebra in PostGIS?

Are there efficient ways of finding the union, intersection, and difference between two bounding boxes (box2df)? I know that this can probably be done by first converting the bounding box (BOX2DF) to ...
2
votes
1answer
32 views

How to activate “explode hstore field” in QGIS 3.6

In How to categorize features based on hstore values in QGIS? somebody said: "In QGIS 3.6, you can use the "explode hstore field" which will create one field for each keys in the hstore field" but i ...
1
vote
1answer
35 views

Storing date in m-dimension of PostGIS geometries

Do you know if it is possible to assign a date (whatever the format) as the 4th dimension to a geometry in PostGIS? If so, does it recognize it as a date?
0
votes
0answers
20 views

How to update geometry in PostGIS for features that are in an ArcGIS enterprise geodatabase (SDE)?

UpdateGeometrySRID is not working for some reason, I think this may be due to it being an enterprise geodatabase? I also tried the manual version of UpdateGeometrySRID, where you alter the table and ...
1
vote
1answer
62 views

Hausdorff distance (ST_HausdorffDistance) extremely slow for polygons?

I have a simple SQL statement for computing the Hausdorff distance between two polygons of the form: SELECT ST_HausdorffDistance(geom1, geom2); , which is taking a long time (for a pair of polygons ...
1
vote
1answer
36 views

Table to Distance Between in PostGIS

I am a new user in PostGIS. I need to create a table with the ID_DIR of table A, ID_SQ of table B and have a new column called "Distance" that would be the distance between the ID_DIR and the ID_SQ in ...
1
vote
0answers
28 views

Return County name from address using PostGIS?

I'm using PostGIS to geocode address data like this: SELECT pprint_addy(addy), ST_AsText(geomout) FROM geocode('Chicago, IL'); I've loaded all the TIGER data for each US state from the US census ...
1
vote
1answer
51 views

GIST index not used by query optimizer on geometry field

I'm trying to add elevation curves on an OpenStreetMap tile server I'm currently building. I created a table in PostgreSQL to store the shapes of the whole world. Structure was defined by gdal_contour ...
2
votes
2answers
50 views

PostGIS: Calculating the shortest distance between one point and MULTILINESTRING objects

I have a table with MULTILINESTRING objects and I want to build a query that outputs the shortest distances between every MULTILINESTRING object and a specific point (the same for all records) using ...
2
votes
1answer
32 views

Getting “missing FROM-clause” error when adding ST_Intersects constraint to PostGIS table

I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added. Obviously dams have to cross a river ...
1
vote
0answers
83 views

Uncompressed vs Subdivided: which one offers better PostGIS INTERSECTS performance?

In my PostGIS I have a point table (roughly 51 millions rows) and a multipolygon table. I need to find a polygon label/ID for every single point in my point table (roughly 51 millions rows) in PostGIS....
1
vote
1answer
36 views

Distance between two point layers

I have a main addresses point layer and a areas point layer. Both layers have XYZ coordinates in the attribute table. I need to create a table based from the addresses layer which calculates the 3D ...
0
votes
0answers
19 views

Submit form in leaflet popup makes blank page

I have a problem with a leaflet popup. I made a form in it and when I try to send form with jquery & AJAX to postgis, data inserts correctly but blank page loads. How to update popup with response ...
0
votes
0answers
41 views

Fitting many rectangular shaped polygons into larger irregular shaped polygon in PostGIS?

I am looking to try and find the optimal fit for a number of rectangular shaped polygons into a larger irregular shaped polygon and I haven't found much information about how this could be achieved in ...
0
votes
1answer
38 views

ST_SnapToGrid doesn't work properly, e.g. 41.94186153740355 -> 41.941861499999995

I'm using ST_SnapToGrid: ST_SnapToGrid(somePoint, 0.0000001) and I get strange result on many points, such as: - POINT (21.619820510769063 41.94186153740355) -> POINT (21.6198205 41....
1
vote
0answers
44 views

Geometry type (Point) does not match column type (LineString)

I'm using PostGIS 2.5.2 on PostgreSQL 10, and I'm trying to build a function which would split a LineString into portions, depending on points positionned close to it. I'm getting a Geometry type (...
0
votes
0answers
25 views

Box Select using leaflet by modifying box zoom with turf intersect

I have been trying to select multiple polygons using box select. I have modified box zoom for this purpose and included turf.js for intersect function. The problem is if i select a particular area and ...
1
vote
0answers
42 views

Convert meters to radians

I'm doing a project in PostGIS where I need to know what is the conversion of meters to radians. I would like to know how to do or what to look for to carry out the doubt.
1
vote
1answer
42 views

Using ST_Buffer on a geometry in SRID 3035 changes location

I'm trying to buffer a linestring using PostGIS and get very strange results. Using this SQL in QGIS: SELECT row_number() over () AS id, * FROM (SELECT st_asewkt(ST_Buffer(geometrie,20)) AS geometrie ...
1
vote
2answers
87 views

Spatial query with clip (PostGIS)

I'm making the transition from GIS to PostGIS and am stuck on this spatial query. I have background geometry (purple, outlined red) which has an overlay of islands (blue, below); I need the area of ...
2
votes
0answers
32 views

PostGIS -> LineString + PointZ = LineStringZ - Misjoined segments

I have used modified version of a querry from this post (I work on linestrings) and have came across few issues: if I run the querry on my table it doesn't join the segments back in the right order ...
0
votes
0answers
32 views

Does ST_GeomFromText find exact matches for provided string or it finds similar, but not 100% same string?

SELECT * FROM centroids WHERE centroid = ST_GeomFromText('POINT (21.6198205 41.9418615)', 4326); Record have centroid geometry equal to POINT (21.619820510769063 41.94186153740355) Running on ...
1
vote
0answers
56 views

Change in PostGIS database password prompts for user credentials on QGIS start

I am using QGIS 3.4.6 and I have a project file which was set up with a PostGIS database connection with basic authentication prompting for a username and password. This was stored as plain text in ...
2
votes
1answer
82 views

Converting MULTIPOLYGON field stored as text to geometry data type in PostGIS and plotting in QGIS

I have this column Multipolygon which has data stored in this format which is in TEXT: MULTIPOLYGON(-115.528984981578 51.2006109579736, -115.526434407604 51.1974145241941, -115.546464451 51....