Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
5
votes
2answers
2k views

Merging polygons that intersect by more than a specified amount

I am working with a dataset that contains a subset of property boundaries in a local government area, thus my dataset is made up of several thousand properties but not all are touching. Since it is a ...
5
votes
3answers
3k views

How to make pgRouting faster?

I have table network which has about 2 million records (only 3 countries in Europe). When I run Shorthest-path it is very slow, it takes 3 min to take records only. I tried with dijkstra-sp-delta-...
5
votes
2answers
511 views

Trouble with PostGIS and multiple selection sources

I'm attempting to take geometry columns from two different tables, perform basic PostGIS operations on them, and have them returned in a single result set. My SQL statement looks like the following: ...
5
votes
2answers
183 views

Postgres/PostGIS skip intersect on features already intersected

I have a collection of polygons(building outlines called "Build_poly") that I am intersecting with another table of flood defenses (also polygons called Flood_poly). When ever one of the building ...
5
votes
1answer
5k views

Query field disabled in edit data mode in pgadmin 4 [closed]

I want to make a SELECT statement in the "View/Edit Data" mode in pgAdmin 4 but the query field is disabled. How can I enable it? (I have all privileges for all, the database, schema and table and I ...
5
votes
1answer
7k views

Insert point into point column in postgres/postgis

I'm trying to insert point data in my database, but it tells me the data types aren't correct (and I think they are). Here's my table: Column | Type ------------+---------- coordinates | ...
5
votes
1answer
528 views

PostGis and datatypes WKT

I dont't have any experience with the geographics databases so I hope that you be patiente with me, the thing is: I know that the datatype geography is used if all you care about is measuring ...
5
votes
3answers
630 views

GIN/GiST Full Text searches through openlayers, geoserver and postgres

I have a a web app that automatically maps recent tweets and allows you to search for certain keywords. it uses Postgres for the database, geoserver as the server, and openlayers as the cartographic ...
5
votes
2answers
5k views

Split Line at intersection and attach attributes

I want to slit line at intersection and attach attributes and export I have roads table with columns id,road_name,road_type,geom with 2115 records. I try using this query - select st_astext((st_dump(...
5
votes
3answers
10k views

Connnect to PostGIS db using QGIS - when not on localhost

I"m just getting started with PostgreSQL/PostGIS db (v9.2) and I'm wanting to connect to db while not on the localhost using QGIS. I've been able to connect successfully on localhost, however I'm not ...
5
votes
2answers
8k views

PostgreSql query problem with aggregate functions

I need help with a PostgreSql query. I need to find all parcels which have bigger area then average. I'm using this query: SELECT OBJECTID FROM parcela WHERE ST_Area(geom)<(SELECT Sum(ST_Area(...
5
votes
1answer
2k views

Convert native box to geometry

I have an old table that contains bounding boxes of lat long coordinates in a "box" type. How do I convert them to PostGIS geometry ? I tried using ST_Transform(ST_SetSRID(se.bound_box::box2d::...
5
votes
2answers
2k views

PostGIS: incorrect interpretation of a polygon that intersects the 180th meridian

I try to find out all geopoints which intersect the polygon set as a parameter. The problem is when I pass polygon that roughly covers an area of Bering Strait (nearby 180 longitude): So I use the ...
5
votes
2answers
621 views

How to put 20K points on a map

I have an sqlite 3 database with 20K entries each of which has lat long data and I wanted to create a layer from that data. From what I have read I need to import it into a postrgres DB but I'm not ...
5
votes
2answers
82 views

Calculate values for all lat/long in another table based on where calculation

I'm working with data in Postgresql+PostGIS and I've stumped myself (probably from overthinking it). For each lat/long in a site table, I want to calculate an average of a field for all points within ...
5
votes
1answer
2k views

Selecting closest points from a given point on another table

I have two tables, one with "houses", and the other with "points". I know how to get the distance individually, like this: gisdb=# SELECT round(ST_Distance_Sphere( (SELECT coordinates FROM houses ...
5
votes
1answer
1k views

Opposite of ST_intersection

I am trying to clip a series of lines selecting only the geometry portion outside of a polygon using PostGIS. From my reading on what st_intersection does i.e. Returns a geometry that represents the ...
5
votes
1answer
2k views

Import Foreign Schema Fails with “schema postgis does not exist”

I am connecting to a Postgres 9.4 foreign server instance where Postgis 2.1 was installed using CREATE EXTENSION postgis WITH SCHEMA postgis from a Postgres 9.6 Beta where Postgis 2.3dev was installed ...
5
votes
1answer
752 views

Displaying data from a partitioned PostgreSQL / PostGIS table is unacceptably slow in QGIS

I have two very similar datasets of 27 million polygons stored in PostgreSQL / PostGIS. One is loaded as a single table, with spatial indexes and regular indexes on key fields. The other is ...
5
votes
2answers
1k views

Adding vertices to polygon in PostGIS?

For example I have rectangular polygon (4 vertices). I want to add mid-point on each line of the polygon, so in total now I have 8 vertices. And from them I want to build new polygon who will have ...
5
votes
1answer
804 views

How to set/update columns by using a trigger?

I want to set/update attributes (alignment_horizontal, alignment_vertical) of a PostGIS geometry (point) by using a trigger. CREATE TABLE point ( gid serial NOT NULL, geom geometry(point, SRID), ...
5
votes
1answer
8k views

Importing shapefile into PostgreSQL/PostGIS?

I'm completely new to PostgreSQL and PostGIS. I finally got both installed and have the PostGIS extension installed. I'm running the PostGIS shapefile import manager. I select my shapefile and click ...
5
votes
1answer
1k views

Close linestring to linering in PostGis

is there a function which closes a linestring to a linering? I couldn't found such a function in the documentation.
5
votes
1answer
3k views

Order by geometry columns

I am wondering how it really works, I mean "order by" statement when it comes to geometry/geography columns in postgis/postgresql. Besides that really need a way gather rows from a table with a point ...
5
votes
1answer
3k views

Polygon from line creation problem

I have a problem transforming a linestring into a polygon. The error is: ERROR: lwpoly_from_lwlines: shell must be closed So I checked but firstpoint and endpoint are the same. I tried to use ...
5
votes
1answer
1k views

How to properly establish a PostgreSQL connection using QgsCredentials

I'm trying to establish a Postgres connection using QgsCredentials as described here: How to access user credentials in database connection? The only problem is that I cannot establish a working ...
5
votes
1answer
1k views

Editing PostgreSQL geodatabase table using ArcPy?

I'm trying to update a table using a python script and it's not working. The table resides in a postgres geodatabase and is versioned. I have also verified my user permissions (I'm the admin). The ...
5
votes
1answer
790 views

ArcMap error drawing postgis layer - The number of points is less than required for feature

I have one big polygon called huc and a multilinestring called streams, I am running an intersection on between the two layers to clip the streams to the huc. here is what I have so far drop table ...
5
votes
1answer
2k views

How to change character set in all columns of many tables in Postgresql / PostGIS?

I have inserted hundreds of tables (from shapefile format) in a Postgresql / Postgis database with the shp2pgsql tool. But because I have encoding issues, I want to change character set in all columns ...
5
votes
2answers
470 views

Calculating Slope for the Whole World in PostGIS

The Challenge I have a PostGIS Database which contains all of SRTM v4.1. I need to calculate slope for the entire globe as part of an error modeling process. The DEM is tiled at 100x100. Whenever I ...
5
votes
1answer
520 views

Storing GPS and mobile sensor data

I am making an app to store GPS and mobile sensor data. My problem is how to store the data. The accelerometer data has a rapid frequency and the GPS data comes irregularly. Right now I've been ...
5
votes
1answer
700 views

ST_MapAlgebra and combination of four rasters

I am trying to create combination of four rasters, but I am lost with ST_MapAlgebra callback function. Official documentation (http://postgis.net/docs/RT_ST_MapAlgebra.html) gives an example, that ...
5
votes
2answers
638 views

Using SSURGO data without MS Access?

I'm building a GIS for a large-scale family farm. We don't have a lot of resources to devote to this, as it's a new part of the farming operation and therefore viewed with some reserved skepticism. ...
5
votes
1answer
129 views

Separate isolated polygons using PostGIS

I am new to PostgreSQL and have been trying to separate polygon features in a PostGIS Table into 2 different Tables based on a condition. All polygons which touch or overlap with one another needs to ...
5
votes
1answer
191 views

Inconsistent results in ST_Distance

I have been playing with the ST_Distance function available within PostGIS and to my surprise, I have encountered some rather strange behavior... In the simplest setting, I have just two tables N and ...
5
votes
2answers
443 views

How to use ST_Difference to create linestrings from non-intersecting polygon boundaries?

There are two tables with polygon features in my PostgreSQL database. I want to create linestring features where the boundries of the polygons are not intersecting. CREATE TABLE schema.poly_a ( gid ...
5
votes
1answer
1k views

PostGIS nearest point with LATERAL JOIN in PostgreSQL 9.3+

I need to obtain on each element on one table the closest point of another table. The first table contains traffic signs and the second one the Entrance Halls of the town. The thing is that I can't ...
5
votes
2answers
1k views

PostGIS Topology Requires Admin

I am running PostGIS on an Ubuntu server. I am able to add the Topology extension via CREATE EXTENSION, but when I try to use it as anything other than an admin I am denied permission. I ran GRANT ...
5
votes
3answers
3k views

PostGis: Geometry from UTM text

I have plain data in a postgresql table which looks like this: ╔════╦═══════╦═════════════╦══════════════╦══════════╦═════════════════╗ ║ id ║ index ║ easting ║ northing ║ utm_zone ║ ...
5
votes
2answers
3k views

Working with address data extracted from *.osm

My goal is get all the addresses from a certain country, ideally - from all countries, but for now - only from one. I downloaded an *.osm file from here http://download.geofabrik.de/ for a certain ...
5
votes
2answers
885 views

3D KNN index in PostGIS

I am looking for a way to do fast KNN queries on a dataset of 10+ million 3D points (and growing). I was hoping PostGIS would do the trick with its new <-> and <#> operators, but they don't seem ...
5
votes
1answer
2k views

PostGIS: Merge multiple table with same GID

I just imported some shapefiles with the PostGIS importer. Because several errors and issues I had to split my shapefile in multiple parts. Then I imported them one by one. I thought I can easily ...
5
votes
2answers
2k views

How to install PostGIS on Linux Mint 14?

I am trying to install PostGIS on Linux Mint 14 KDE. First thing I tried was to install PostgreSQL using "sudo apt-get install postgresql". I was successful doing that. I then did "sudo apt-get ...
5
votes
1answer
463 views

PostGIS Trigger doesn't work

The trigger below don't works and I need that when a new pipeline (adutoras) is inserted or updated, the procedure check what are the municipalities (municipios) that intersects the pipes and put the ...
5
votes
2answers
275 views

How can I join a spreadsheet to a point layer?

I have a layer with point data for the grave positions in our cemetery. We are gathering data from each headstone. If I enter the data to a spreadsheet can I later associate the spreadsheet with the ...
5
votes
1answer
1k views

Determining suitable server specification?

I am looking for server specifications for a moderately sized habitat mapping project. My current server is about to reach its storage capacity, so I am researching buying a new one that I can ...
5
votes
1answer
523 views

How to update attribute values of points outside an area?

I have two datasets in PostGIS 1.5 (on Postgres 9.1) one of polygons and one of points. I am hoping to create a SQL command that will select all of the points in a dataset that are not within of the ...
5
votes
2answers
462 views

Selecting POIs around specific buildings using PostGIS

The problem: I need to select, for each residential building in my table that has say at least 2 pharmacies and 2 education centers within a radius of 1km, all POIs (pharmacies, comercial centres, ...
5
votes
1answer
849 views

Error using ST_AddPoint for adding a new point to a polyline in PostGIS

I have two PostGIS tables, one where I store points, named "nodes" and another with where I store multilinestrings, named "lines". I'm trying to append a new vertex to a line using one of the nodes ...
5
votes
1answer
87 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 ...