Questions tagged [postgis]

PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.

Filter by
Sorted by
Tagged with
15
votes
3answers
8k views

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
14
votes
2answers
10k views

PostGIS vs. SQL Server for GIS data

So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue ...
11
votes
2answers
37k views

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to "/...
11
votes
1answer
1k views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
9
votes
2answers
4k views

Order of columns in a compound index in PostgreSQL (and query order)

I have a table with 50K rows. It is actually a PostGIS table. The query has 4 parts (1 mandatory) (3 Optional) intersection box (a geography rectangle) with 4 lat,long (I use st_intersects) [...
8
votes
1answer
484 views

PostgreSQL/PostGIS 9.6 broke my compound index

In PostgreSQL 9.2 I had no problem creating the an index that had both a geography (postGIS) type and and integer as a compound index. But now (9.6) it complains on creation of the index and I don't ...
8
votes
1answer
1k views

moving large PostgreSQL/PostGIS database

I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1). The upgrade process is well-documented. The ...
7
votes
1answer
4k views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
7
votes
1answer
591 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
6
votes
1answer
12k views

Limiting number of results in a Partition using OVER(PARTITION BY)

In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
6
votes
1answer
6k views

Problems installing Postgis extension on Ubuntu 14.04

I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple: sudo apt-get install postgresql-contrib ...
6
votes
3answers
2k views

Install PostGIS v2 for PostgreSQL v9.2

I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension. I've been searching for it for 3 days now, however can't find it. I did however find ...
6
votes
1answer
459 views

Troubleshooting Postgresql spatial query failure. Next steps?

We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be ...
5
votes
2answers
2k views

Pros and Cons of virtualisation technology for database-server and datastorage

Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm ...
5
votes
1answer
90 views

Store both geometry and geography or store one and calculate the other?

I am just starting out designing my first GIS database, using PostGIS / PostgreSQL, and I have a question regarding geometry and geography, specifically whether it is more efficient to store geometry (...
5
votes
1answer
837 views

Using SELECT to call a function in a jdbc migration

I'm looking to set up some Postgres/PostGIS migrations with clojure/jdbc. running side-effect functions with SELECT is proving to be an issue, with most migration libs eventually throwing the A ...
5
votes
1answer
869 views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table teste....
4
votes
2answers
508 views

Support for libprotobuf-c on Amazon RDS

I created a PostgreSQL 10 instance on Amazon RDS with the PostGIS 2.4 extension in my database enabled. I want to test the performance of Amazon RDS as a dynamic vector-tile server. This hinges on ...
4
votes
4answers
694 views

Best way to model a database structure to store multidimensional variables?

I am working with some NetCDF data which has multidimensional variables. As far as I'm aware, the fields are not mandatory. Each variable will have the same fields/attributes. Each variable ...
4
votes
1answer
821 views

Error in PostgreSQL/PostGIS Access to offline bands disabled

I am using postgis functions i.e. ST_Clip, ST_PixelHeight. But I am getting this error - ERROR: rt_raster_load_offline_data: Access to offline bands disabled Actually, I have loaded my raster to ...
4
votes
2answers
602 views

Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?

Inserting multiple rows into a table with a single INSERT INTO statement fails (as expected) when some of these rows have values of a PostGIS geometry type incompatible to the respective row's PostGIS ...
4
votes
1answer
2k views

How do I render GeoHashes with PostgreSQL?

Are there any geohash implementation for PostgreSQL? I couldn't find any.
4
votes
1answer
2k views

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases: /usr/lib/postgresql/9.3/bin/pg_dump --schema-only -h stg-db.....rds.amazonaws.com ...
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
3
votes
1answer
1k views

&& vs ST_Intersects performance

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ...
3
votes
1answer
116 views

postgis query id to multiline query

I need some insight on how to get data out of a telemetry DB in just the right way and fumbling with this produces either errors or incorrect data I have a table like this: CREATE TABLE Telemetry ( ...
3
votes
1answer
503 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
3
votes
3answers
2k views

QGIS PostGIS Authentication Failure with “trust” option

I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, ...
3
votes
1answer
284 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE UNACCENT(LOWER(a.name)...
3
votes
1answer
8k views

Postgresql subquery speed much slower than individual queries

In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal code....
3
votes
1answer
37 views

How to “group” based on consecutive occurrences of an element in a column in postgres

I have a table that, when ordered, describes a path by point descriptions in each row. A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts ...
3
votes
1answer
2k views

Finding the nearest geo points across two tables?

I have two tables, both have lat/lon columns within same country (UK). Tables sizes are roughly 80M and 50M. Along with the lat/lon columns I have created a geo-indexes for both tables in this way: ...
3
votes
1answer
259 views

What should I do to get my RDS instance running PostgreSQL 9.6 to perform at least as well as my instance running PostgreSQL 9.3?

I am attempting to switch our Amazon RDS instance from PostgreqSQL 9.3 with PostGIS 2.1 to PostgreSQL 9.6 with PostGIS 2.3. I launched a new instance from a Snapshot and updated the new instance from ...
3
votes
1answer
943 views

Return text from PostgreSQL (9.6) to SQL Server from linked server query

I am trying to get text (15002 characters long) from PostgreSQL using a linked server from SQL Server 2012, using the official PostgreSQL ODBC driver in Windows. I try to pull data from Postgres ...
3
votes
1answer
198 views

RDS raising unwanted PostGIS NOTICEs

I have an Amazon RDS instance that is producing unwanted NOTICEs. I have never seen this behaviour before. mydb=> select current_setting('client_min_messages'); current_setting -----------------...
3
votes
2answers
139 views

Joining tables based on time and distance: PostGIS with PostgreSQL?

I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small": Small Table: id event_time ...
3
votes
1answer
425 views

Trigger to automatically calculate area when adding polygon in PostGIS

I need help with writing a trigger to automatically update and insert into a column the area of a polygon being created using PostgreSQL/PostGIS and QGIS. This is what I have but it's not updating ...
3
votes
1answer
2k views

PostGIS ERROR: operator does not exist: postgis.geometry @ geometry

I have had PostgreSQL 9.3.1 and PostGIS 2.1.0 installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby (2.0....
3
votes
0answers
36 views

Upgrade PostgreSQL 9.3 without moving the DB and messing PostGIS on OSX OpenGeo Suite original installation

I looked for the answer, but could not find a specifically fitting one, so would appreciate an answer or a pointer to one... Q: How do I upgrade PostgreSQL 9.3 to the current (now 9.6 ?) without ...
3
votes
1answer
48 views

Is it possible to build PostGIS extensions without building the entire project?

PostGIS has an extension called postgis_tiger_geocoder, I have a few questions about it (all pretty tied together), Is it possible to build that without building the rest of PostGIS? If so, can I use ...
3
votes
0answers
117 views

Access points of geometric path data in PostgreSQL 8.1

Summary: I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to ...
3
votes
0answers
314 views

2 versions of Postgis installed

For some reason I manage to have 2 versions of postgis installed at the same time due to a wrong update. I can see this as by issuing a select postgis_full_version() i get back version 2.1.0 ...
2
votes
2answers
920 views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
2
votes
1answer
10k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
2
votes
3answers
545 views

GIS Point or discrete longitude and latitude columns?

I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 ...
2
votes
2answers
1k views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
2
votes
1answer
339 views

Why does this PostGIS query take so long to run?

I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing ...
2
votes
2answers
678 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
2
votes
1answer
188 views

PostgreSQL Geo Spatial Query is slow

After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only. I need to fetch records from a Table ...
2
votes
1answer
175 views

For Postgres, efficiently get points within X distance of point Y?

I have this table as below in Postgres: create table locations ( id serial not null constraint games_pkey primary key, name varchar, location point, ) ; I want to ...