Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgis]

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

283 questions
23
votes
2 answers

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 "/root" and the Password: prompt appeared twice! Is…
Nyxynyx
  • 1,131
  • 6
  • 18
  • 29
21
votes
3 answers

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 with this, we are a 95% SQL Server and 5% Oracle…
LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
19
votes
3 answers

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) [Mandatory] Date Range (min, max) on a date field File…
Dr.YSG
  • 409
  • 1
  • 4
  • 15
17
votes
3 answers

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 spatial polygons. That being said, our database has…
Ryan Charmley
  • 273
  • 1
  • 2
  • 6
15
votes
1 answer

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 || ')' ), ST_GeographyFromText('SRID=4326;POINT(-76.000000…
Gandalf StormCrow
  • 615
  • 1
  • 8
  • 17
13
votes
1 answer

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 different variety of weather data (temp, dew point,…
bshender
  • 241
  • 2
  • 4
12
votes
1 answer

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 understand the hint it is providing: The columns…
Dr.YSG
  • 409
  • 1
  • 4
  • 15
8
votes
1 answer

What's stored in spatial_ref_sys?

Should I copy data in this table when migrating to the other server?
x-yuri
  • 359
  • 1
  • 4
  • 11
8
votes
2 answers

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 problem is I don't have enough space on server1 to…
kontextify
  • 471
  • 1
  • 5
  • 10
7
votes
1 answer

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 ST_GEOMFROMGeoJSON() function causes to server to…
stUrb
  • 757
  • 2
  • 9
  • 15
7
votes
1 answer

&& 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 ST_Intersect. However I think I explained my own question…
yurtesen
  • 247
  • 4
  • 11
6
votes
1 answer

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 postgis postgresql-9.3-postgis-2.1 However, this…
gregoltsov
  • 165
  • 1
  • 8
6
votes
1 answer

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 subquery with WHERE row_num < 3? Query SELECT pid,…
Nyxynyx
  • 1,131
  • 6
  • 18
  • 29
6
votes
1 answer

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 sure and also so that the resulting bug report is…
gsiems
  • 3,413
  • 2
  • 23
  • 26
6
votes
3 answers

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 PostGIS v2 for PostgreSQL v9.1, thanks to this site:…
Paul Meems
  • 163
  • 7
1
2 3
…
18 19