Questions tagged [postgis]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
9 views

Pixelate Fiji polygons to a set resolution using PostGIS

After some help pixelating polygons please. I have some region polygons for Fiji in CRS 4326. SELECT geom FROM fiji_regions I first add a column like this: ALTER TABLE fiji_regions ADD COLUMN id ...
1
vote
0answers
35 views

Ensuring all points are a certain distance from polygon boundary

I have some PostGIS SQL that generates uniformly distributed points within polygons based on area (roughly following this tutorial). These points represent field sampling locations. Some of the points ...
0
votes
0answers
12 views

Using PostgreSQL to calculate ST_Volume on XYZ data

I have a set of XYZ data which I want to query in PostgreSQL in order to find the volume. I've been trying the ST_Volume on the geometries (have also used ST_MakeSolid and ST_Tesselate) but all ...
4
votes
1answer
126 views

Create a PL/PSQL function that performs as fast as a normal query?

I'm trying to create a PL/psql function that cuts one user-defined geometry to a predefined border geometry. If the user-defined geometry does not intersect with the border geometry, it should return ...
0
votes
0answers
17 views

Comparing two lines

I'm working on a problem that we have lines and a lot of points near the lines we should draw a line between points. the new line should be similar to the first line! for comparing the new line and ...
0
votes
0answers
32 views

PostGIS SQL error in Java: no spatial operator found for st_contains

I'm quite new in PostGIS, and I'm trying to run an SQL query. I use this query to obtain a subset of segments from table roads in a group of polygons, then put the result in a separate table. There ...
3
votes
2answers
121 views

How to include non aggregate columns in select function if not including them in aggregate function (like GROUP BY) in PostGIS table

I have a property parcel table a in PostGIS that has the following columns/fields owner_name, owner_city, county_name, state, owner_address, owner_mailing_address I want to dissolve/aggregate the ...
0
votes
1answer
23 views

Error loading GeoTIFF using raster2pgsql: “Error 1: PROJ: Cannot find proj.db”

I am attempting to load a GeoTiff into a PostgreSQL database using the PostGIS extension raster2pgsql. When running the executable, I receive the following error: Error 1: PROJ: Cannot find proj.db. ...
0
votes
0answers
29 views

Error connecting to a PostgreSQL database from QGIS using SSL certificates

I'm trying to connect QGIS (v3.14) to a PostgreSQL database hosted on Google Cloud. I've successfully connected to the database through pgAdmin (v4.23) so my assumption is that there isn't a problem ...
1
vote
2answers
33 views

PostGIS: Syntax for ST_Collect into a multipolygon keeping internal boundries

I have a PostGIS table with many polygons representing islands these all form a common group How do I create a new MultiPolygon (keeping internal boundaries) and give it a new name? CREATE TABLE ...
2
votes
1answer
41 views

PostGIS check if multiline string is included in polygon

I want to return all records that the multiline-string is included in the polygon. In the photo below, it should return two records. Green line (streets) is contained inside two neighbourhoods (white ...
1
vote
0answers
34 views

Commit error when splitting several polygons with “array type” attribute (PostGIS / QGIS)

I have a simple PostGIS table : CREATE TABLE public.elem_p ( gid integer NOT NULL DEFAULT nextval('elem_p_gid_seq'::regclass), liste character varying[] COLLATE pg_catalog."default" ...
0
votes
1answer
24 views

PostGIS: why does it return an error when changing geometry to GeometryCollection?

I've got a postgreSQL/postGIS table whose geom column holds Geometry type (MultiLineString). I want to include in the table also Points and MultiPoints, so I tried to change the geometry type with ...
0
votes
0answers
23 views

Dynamically add data to GeoServer [closed]

I would need some advice on which technologies to use for a certain problem. As described below an attempt has been made which is not satisfactory, therefor I'd like to ask for advice. I have GeoTIFF ...
3
votes
3answers
84 views

Missing geometry type and SRID in PostGIS

I created a view with select a, st_union(geom) from table group by a srid: 25832, geometry type: Multipolygon The view has no SRID and the geometry type is "GEOMETRY". Now i want to ...
1
vote
0answers
31 views

Error 0x8004159D while loading data from PostGIS to ArcGIS Pro

I successfully connected to the PostgreSQL database, and I can see the tables/data. When I load one of the data (linestring data) it works well. But when I loaded the polygon data it prompted the ...
0
votes
1answer
41 views

Google Geocoding API to geometry (Multi-line string)

I am using the Google Geocoding API. I need to use that response and convert that to geometry. How is this possible? What postgis function should I use and what parameters to assign? Raw JSON data: &...
3
votes
1answer
45 views

Calling PostgreSQL procedures or functions from QGIS' Query Builder

I am trying to call either a stored procedure or function defined in the PostgreSQL/PostGIS database and use it from the QGIS Query Builder. Neither work: the function returns a table that cannot be ...
0
votes
0answers
10 views

Connecting PostGIS with GeoNetwork

I would like to connect a PostGIS-PostgreSQL database created in pgAdmin to GeoNetwork. Which files in the webInf under the geonetwork folder I am supposed to modify and how?
0
votes
0answers
7 views

pg_dump on tiger.loader_platform not having any inserts

I am trying to backup the tiger.loader_platform" table. There are three rows in the table. I have tried this command on the same same database under the public schema and I am getting insert ...
0
votes
0answers
25 views

GeoPandas and Cartopy how to dynamically zoom to Multiline

I am new to GeoPandas and Cartopy. I am trying to generate map plots for different regions dynamically. When generating the image from geopandas ('df.plot()') the size is correct, but when I generate ...
0
votes
1answer
37 views

SRID not updating

I have created a point geometry using ST_GeomFromText function in PostGIS and simultaneous set the SRID to 3857. Alter table data.trips_42779 add column geom geometry; UPDATE data.trips_42779 SET ...
2
votes
0answers
26 views

How to force PostGIS to use a specific GDAL version in Mac?

I am using PostGIS 2.5.4 on PostgreSQL 12.4 under macOS 10.13 environment. Although GDAL version 2.4 is already installed in "/Library/Frameworks/GDAL.framework/Versions", the query select ...
0
votes
0answers
37 views

Openlayers use geometry to zoom to extent [closed]

I retrieve the geometry of an object from the database. Having that, how can i zoom to the center of a line string using fit in openlayers? Is there a database solution e.g. convert that using a ...
1
vote
1answer
44 views

PostGIS ST_Covers doesn't match polygon after ST_Union

I have a simple table: create table test ( id serial, polygon geometry(Polygon, 3857) ); And I want to insert two polygons in this table which should be created as points expand with ...
1
vote
1answer
24 views

Zoomable world map with small countries highlighted

I'd like to create a zoomable world map where countries/territories are color-coded. This is a pretty standard request and Carto has a nice example. However, in standard maps like this, small ...
0
votes
0answers
11 views

Keep the options of the cells of each column of the attribute table of a layer in QGIS when exporting the layer in a format [duplicate]

Preserve the options of the cells of each column of the attribute table of a layer in QGIS when exporting the layer in a format or when importing the layer in PostGIS with those options in the cells ...
4
votes
1answer
70 views

Cluster points in each polygon into n parts

I am working on adding 2000 random points to each of 2 polygons and then clustering the points in those polygons into 4 clusters for each polygon. When I apply ST_ClusterKMeans() in PostGIS, it ...
1
vote
0answers
37 views

How to query PostGIS for the XYZ tiles covered by features?

I have a feature table in PostgreSQL and I need to calculate the XYZ tiles that the queried features encompass. So my query would look something like this: SELECT getTiles(geom, min_zoom, max_zoom) ...
0
votes
0answers
20 views

How to filter by several parameters in GeoServer from the PostGIS table [duplicate]

On a layer from the GeoServer I linked a table to PostGIS, in that table I have a geometry column and several columns of related geometry information after which it should be filtered. Now it should ...
0
votes
1answer
42 views

ST_HausdorffDistance() mismatch

I have been using PostGIS 3.0. The ST_HausdorffDistance() function calculates a symmetric distance between two polygons. However, Hausdorff distance is not a metric since it is the maximum distance ...
0
votes
0answers
16 views

Is there a way to plot Triangulated Irregular Networks (TIN) in R Jupyter notebooks?

I was wondering if it is possible to plot TINs (Triangulated Irregular Networks) in Jupyter plots (with R kernel)? I use R's sf package (in conjunction with PostGIS) and ggplot2 in Jupyter to plot ...
0
votes
0answers
14 views

PostGIS geocoder with TIGER data. Getting county from address?

I have installed PostGIS with the geocoding extension and have loaded 2019 TIGER data for all the states. How can I get the county for a US address? I would have the full address available. Example: ...
4
votes
1answer
78 views

Finding opposite side of a polygon

I have a polygon and known 2 points. One is the center of the polygon and another is outside the polygon. I'm trying to find the opposite point of the polygon where the line string of the 2 known ...
1
vote
1answer
37 views

Find Largest Building in each Parcel (largest feature in each groupby set)

I have a question similar to this one, but the proposed approach doesn't seem to work. For context, I am new to qgis. Given a set of parcels, each with 0 or more buildings, I want to select only the ...
1
vote
1answer
30 views

How to keep feature ID within GeoJSON ogr2ogr import to PostGIS

I'm attempting to import a GeoJSON file into PostGIS using ogr2ogr The basic command is ogr2ogr -f "PostgreSQL" PG:"dbname=my_db user=postgres" my_file.geojson -preserve_fid -nln ...
3
votes
2answers
134 views

Find polygons that has no points in PostGIS

I have a layer with points and a layer of polygons. I can find all the polygons that contains at least one point inside itself by: SELECT polygons.id FROM polygons, points WHERE ST_Intersects(polygons....
0
votes
0answers
34 views

ogr2ogr PostGIS to GeoJSON using batch file

I am trying to export a PostGIS table to GeoJSON using ogr2ogr. The following works perfectly when I launch it in Windows command : ogr2ogr -f "GeoJSON" F:\output.json PG:"host=**** ...
1
vote
1answer
59 views

GeoServer+PostGIS performance for very detailed data

I have a layer on GeoServer, taking very detailed data from a PostGIS table. I can see the size of the table is quite big Size 977MB, External Size 977Mb becoming the performance really low. GIST ...
1
vote
2answers
38 views

PostGIS - identify duplicate points from polygons/linestrings?

I'm trying to identify polygons/linestrings with duplicate points, using PostGIS. I just want to tell whether the geometry has duplicate points, not remove the extra point. I know there is ...
0
votes
0answers
62 views

Problem with massive data importation to PostGIS/PostgreSQL

I need to import a huge SIG data into PostgreSQL (my shapefile table have almost 1.700.000 rowns and 43 columns). I'm using PostgreSQL 12.4 and PostGIS 3.0.x, that came together with PostGIS Bundle 3 ...
1
vote
2answers
68 views

Non-noded intersection issues from overlapping buffer objects

I am working with valid lat/lon point data, creating buffers, and trying to calculate overlapping areas from buffers and running into the not-so-rare error with non-noded intersections when using ...
1
vote
1answer
25 views

Is there a simple table structure update procedure for a manipulated PostGIS layer under QGIS

I have an intensive data exploring session using Postgresql/PostGIS as a backend and try to explore the results using QGIS. Within QGIS I have a defined a "complex" style to show the results....
1
vote
0answers
26 views

Determine if two geometries are similar in shape and location

Suppose I have two geometries that have almost identical location and shape. I say almost on purpose here, since one or the other might have an extra node, or the order of coordinates in GeoJSON might ...
1
vote
1answer
26 views

PostGIS combine ST_DistanceSphere with Nearest Neighbor search

I have two tables both have point geometry with SRID 4326 (WGS84) I need to find the nearest point in 1 table to every point in another table PostGIS KNN search is written t1.geometry <-> t2....
3
votes
3answers
57 views

Calculating value of unique points in polygon in PostGIS

I'm working with a spatial PostgreSQL database that contains species records, there is a point layer 'spp_points', each point has a "spp_ID" and "spp_score" field. I also have a ...
1
vote
1answer
44 views

shp2pgsql: not found on Debian 10 PostgreSQL 13

I have installed PostgreSQL 13 by using the following repository deb http://apt.postgresql.org/pub/repos/apt buster-pgdg main and I have installed PostGIS using apt install postgresql-13-postgis-3 ...
1
vote
1answer
42 views

PostGIS ST_AsGeoJSON throws error

I'm inserting Open Street Map data into a PostGIS database using the GeometryBuilder of osm4j. I then fetch the data and display it on a Leaflet map using ST_AsGeoJSON. This usually works flawlessly, ...
0
votes
1answer
57 views

User-friendly maintenance of the PostGIS geodatabase [closed]

We have a PostGIS geodatabase that is populated by several people. The base is gradually growing, and it becomes unclear to users what data we have and how to find it. In fact, only the person who ...
1
vote
0answers
64 views

Join the result of an SQL query from PostgreSQL to QGIS

I want to send an SQL query from a Python script in QGIS to PostgreSQL/PostGIS and get back the result as a table which I then join in QGIS to a shapefile. How do I do that? At the moment I try to ...

1
2 3 4 5
155