Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

0
votes
0answers
11 views

PostGIS trigger to remove duplicated nodes on update

I have a database with POLYGONs. I want to remove duplicated nodes of the edited/inserted polygon. I made a function: CREATE or REPLACE FUNCTION ov_geometry_cleaner() RETURNS trigger AS $$ Begin ...
1
vote
0answers
26 views

Import ArcSDE db to Postgres/PostGIS

Is it possible to import an ArcSDE db in PostGIS? I don't have experience with ArcSDE and then I don't have any idea to what is the data exportation format of this software. A customer asked me to ...
2
votes
1answer
32 views

Slow PostGIS line intersection even with indexes

I have line data contained in different graphs. Querying data over multiple graphs and using st_intersects results in slow queries. All lines are represented as geometry(LineString). I am querying ...
1
vote
0answers
15 views

Raster Average Across Tiles

I have a raster that is tiled and I am looking to produce an average raster with a 3x3 window. I have tried three methods. The first method uses the standard ST_Mean4ma. An average raster is produced, ...
0
votes
0answers
10 views

Where is flat-nodes file located?

I would like to use osm2pgsql to load the europe.osm.pbf to the database. I've already read about the optimalization to make the osm2pgsql do it as fast as possible. However, there's one option, that ...
1
vote
1answer
37 views

Incorrect extent when importing shapefile to PostGIS

Using PostgreSQL 9.6, pgAdmin 4 v3.5 and QGIS I’ve found quite a bit of info on this issue but no clear answer. I’m attempting to import a shapefile that was created using ArcGIS (don’t know what ...
1
vote
1answer
22 views

How do I create a view in Postgres?

I have two layers. Layer one is a multipolygon layer (can be split to single polygons if needed). Layer two contains points, lines and polygons. Using layer one, I want to spatially filter layer ...
0
votes
1answer
31 views

Spatial join attributes based on closest point in PostGIS

I have two point tables from which I want to extract a value from one table to the other, based on the nearest distance of those two points. t1 t2 id | value id 1 | 100 1 ...
1
vote
1answer
21 views

Cross-schema function call with PostGIS dependencies

I have an issue where I am unable to make a cross-schema function call because of that function's use of PostGIS. I have both PostGIS and my function in the public schema, but I'm trying to call the ...
0
votes
0answers
16 views

pgr_astar function changing the heuristic function

I'm using pgr_astar function of PostgreSQL, I'm aiming to find the most fuel efficient path. So, I precises the cost of fuel and the reverse cost of each road segement. The problem that the heuristic ...
0
votes
0answers
24 views

How to display on PostgreSQL the shapefile metadata

I'm a beginner on PostgreSQL/PostGIS. I'm working in a project using a spatial data base which I'm trying to display, on PostgreSQL, the shapefile metadata of each file. As every metadata is on ...
1
vote
1answer
28 views

How should variable data related to a point be stored in a database table? [on hold]

I've been having a bit of trouble understanding how a dataset is built/managed for GIS applications. Over the past few weeks (part time) I've been trying to understand the basics of GIS, and how it ...
2
votes
2answers
121 views

How to create a rule on QGIS to show only features that have last week as date?

Basically I have a PostGIS layer with a date column. I only need to show features that have the last week as a value in the date column. How to achieve this? Unfortunately now() - 1 week doesn't ...
0
votes
1answer
59 views

Uploading shapefile to PostGIS database using PHP

I want to insert a shapefile into a PostGIS database using PHP. I go through the examples and I tried the following: $command = 'shp2pgsql -s 2100 -d /D:\CHECK DATA-group\2016\Ayomi\JULY\...
3
votes
1answer
88 views

Accessing PostGIS data in QGIS?

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS ...
2
votes
2answers
89 views

Why is creating features with FALSE boolean fields in QGIS and Postgres failing when the fields have default values?

I'm having problems with adding rows that have boolean columns to Postgres tables from QGIS. In a nutshell, shapes with boolean fields can only be created when the booleans are set to true (updates ...
0
votes
0answers
12 views

Region Group And Zonal Area in PostgreSQL

I am looking for the PostgreSQL equivalent to the ArcGIS Region Group and Zonal Area. Specifically, I have a raster of 1's and 0's. I want to be able, for each group of 1 values, tell the area for ...
0
votes
0answers
18 views

Does pg_createTopology not create all topologies?

I created my paths from QGIS v2.18.18 and imported the shp file to pgadmin using GDAL with this command: ogr2ogr -f "PostgreSQL" "PG:host=localhost user=postgres dbname=routing password=password" "F:...
2
votes
1answer
33 views

Speeding up ST_Value in PostgreSQL?

I am working on a large point file and am looking to get the raster value underneath each point. I am using: UPDATE Locations a SET image3b3_sum = ST_Value(rast, 1, geom) FROM image_sum_sum; 10 ...
0
votes
0answers
21 views

SQL Query OSM road types in a bounding box

I have a basic command that works to query all planet_osm_line features within a bounding box (running a PostgreSQL db of the entire OSM planet file) SELECT * FROM planet_osm_line WHERE ...
0
votes
0answers
31 views

pgr_dijkstra() giving out a weird path?

I'm wondering why my pgr_djikstra is giving me this path, My path is was created from QGIS v2.18.18, upon creating my lines I used Cleaning Tool and Explode Lines Tool to get my desired lines, then ...
2
votes
3answers
25 views

How do you create buffers around points based on individual point values in CARTO?

I am very new to CARTO and have very limited coding skills. I have a data set of points representing the centroid of zip codes. Each point has an associated average commute time. It is relatively ...
1
vote
1answer
24 views

Why does my source and target repeats itself when I use pgr_createtopology()?

I created my path in QGIS v2.18.18, making the lines and a combination of cleaning tool and explode lines tool to get my desired output. I tried using ROAD GRAPH plugin in order to check whether my ...
2
votes
0answers
30 views

Hosting WMS layer using Geoserver, Tomcat, Amazon AWS?

My goal: provide clients with a password protected WMS url so they can access some of our GIS data. Where I'm at so far: OS: windows 8 I have a polygon shapefile created in QGIS I want to share as ...
0
votes
0answers
38 views

Can't upgrade PostGIS on Ubuntu with Postgres 10?

I am running Postgres 10.5 (I think) on Ubuntu 14.04. I want to upgrade to PostGIS 2.2+ so I can use ST_Subdivide. I've done: apt install postgresql-10-postgis-2.4 But when I try to upgrade PostGIS ...
1
vote
2answers
62 views

How get intersection of polygons in the same table by PostGIS?

I have a single table of over 1000 polygons in postgreSQL (with PostGIS), and I have a field "evaluation_type" that only receives two values, either 'good'(green) or 'bad'(red), as you can see in the ...
0
votes
0answers
33 views

ogr2ogr merge fgdb (while importing to PostGIS)

I have millions of records and hundreds of tables in several file geodatabases.. The tables name in these FGDBs are all same but just in different areas. For instance, the Admin table, you see below,...
2
votes
0answers
31 views

PostgreSQL/PostGIS: Adding attributes to a table that I can use to query with

Currently I have a PostGIS database that is taking in shapefiles. However, these shapefiles are converted into tables when they are imported into the database. Each shapefile deals with a farm name, ...
0
votes
1answer
15 views

ogr2ogr import fails with “ERROR 1: GeoJSON object too complex”

I'm trying to import a large (8.8GB) GeoJSON file to a Postgres 9.6 database with the following command: ogr2ogr -f "PostgreSQL" -nln final_diffs PG:"host=localhost dbname=inspire user=me" diffs....
2
votes
0answers
32 views

PostGIS geography returns area < 0.0

I am trying to convert a geometry column to a geography column and calculate the area using this query: SELECT dccode,SUM(CAST(St_Area(geom::geography,true) AS NUMERIC)) as Area FROM schema....
3
votes
0answers
34 views

Problem with insert into PostGIS view table via QGIS

I use a PostgreSQL 9.5.14 (PostGIS) and QGIS 2.18.7. The problem is that when I try to add a new object to the View via a QGIS, the following error occurs: ERROR: column "geom" is of type ...
1
vote
0answers
36 views

Shapefile import failed in PostGIS

I am new to PostGIS/PostgreSQL and have downloaded and installed to perform a spatial join which ideally takes 27 hours in ArcGIS Pro and 19 hours by ArcPy.Hoping for a faster results via PostGIS from ...
3
votes
1answer
43 views

Getting the distance traveled across road segments

so I have a query that takes an encoded polyline, segmentizes that polyline into smaller pieces, and matches each point on that segmentized line to a "road segment" from a separate table in my ...
3
votes
2answers
46 views

FME PostGIS writer define column as serial/PrimaryKey with pre-existing id values

With FME I read a table that has an id column with pre-existing values, like id | some_attributes... -------------------------------- 56784645 | ... 56478687 | ... ... I would like to write ...
3
votes
1answer
34 views

Can't get ST_MapAlgebra to return fractional values

I have a 4-band raster dataset that I have imported into a Postgres/PostGIS table using raster2pgsql. I am Using ST_MapAlgebra() with a callback function to perform some band math on the dataset. I ...
3
votes
1answer
30 views

Making UPDATE for coordinate does not return the same value

Being totally new with PostGIS, I am trying to UPDATE one of my column on my table. The column itself is called 'center_point' with type of GEOGRAPHY(Point,4326) UPDATE cities SET center_point = ...
1
vote
1answer
27 views

PostGIS -> LineString + PointZ = LineStringZ

DATA I have 2 tables in PostGIS: linestable with MultiLineString geom surveypointtable with Point + elevation attribute GOAL The goal is to rebuild the lines as 3D replacing line's vertices with ...
1
vote
1answer
32 views

ST_SymDifference with dblink

I want to create a table (table_diff) that is a result of symmetrical difference (ST_SymDifference) between geometries (linestrings) from 2 tables (table1, table2) stored in 2 databases (db1, db2). ...
0
votes
2answers
21 views

UPDATE geometry in table for a specific row

GIVEN: gisTable (Table) has a geom (geometry) column defined as follows: ALTER table gisTable ADD column geom geometry (Multipolygon, 4326); I am attempting to run the UPDATE command below which ...
1
vote
2answers
53 views

PostgreSQL select value where '100%' using bigint

I am trying to select data from a bigint column in PgAdmin using SQL: SELECT FROM public.addressbaseplus WHERE uprn LIKE '100%' However, I am getting the following error: ERROR: operator does not ...
1
vote
2answers
45 views

Refresh ST_Area in QGIS

I added a new column in my PostgreSQL/PostGIS database for getting surface area. I can open as a layer in QGIS, but when I draw a new polygon the area column is not updated, is there a way to have it ...
4
votes
1answer
126 views

Returning US Census geographies within range of coordinate using PostGIS and Tiger Decoder?

TL;DR: How do I write a query in SQL (postgres, postGIS, tiger extension) that will return census geographies (blocks, tracts, zcta) within an X mile radius of a lat/lng coordinate? Background: I ...
0
votes
1answer
46 views

ST_DISTANCE isn't giving the expected result

I don't get why ST_DISTANCE is giving me the same closest node when clearly it is not the one nearest to the starting point. Also, using 2 different starting points, I still have the same result. My ...
1
vote
1answer
28 views

Update Set_Area from geometry

I am very new at PostgreSQL / PostGIS. I have got a table with geometry column set in (Polygon,32733), so I would like to update the Area column so as to see the area of each polygon. I tried this: ...
0
votes
0answers
24 views

rt_raster_from_two_rasters: The two rasters provided do not have the same alignment

I have a set of geometries in my database which I used to create a set of rasters (each raster has a unique color). Now I would like to merge all of these rasters into one but get an error such as : ...
0
votes
1answer
37 views

Why does converting the_geom into lat/long gives me more than 1 coordinate?

I'm converting my the_geom into lat/long coordinates but I only want a single coordinate since I'm gonna use the coordinates to draw a polyline in google maps. Attached is the picture of the result of ...
0
votes
1answer
20 views

PostGIS ST_RECLASS using Lookup Table not working properly

Link to question/answer similar to what I am trying to do I am trying to run ST_RECLASS on a raster to retain certain pixel values while setting others to NoData(0). I have a function that creates a ...
0
votes
2answers
40 views

How to optimize this query for (OpenStreetMap) map tiles rendering?

I'm trying to optimize my OpenStreetMap map tiles rendering. This is one of the queries renderd makes to PostgreSQL: SELECT ST_AsBinary("way") AS geom, "type" FROM (SELECT way, ...
1
vote
0answers
40 views

Create raster layer from points with PostGIS

I'm pretty new to this part of GIS; I have a layer of points that represent cell phone towers; with a given distance to simulate coverage, how can I transform that represented coverage into a raster? ...
0
votes
1answer
35 views

PostgreSQL Query which returns geom column as WKT set in function

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[...