The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
0answers
3 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
1
vote
1answer
23 views

Unexpected results with soundex() function when combined st_distance()

select soundex(A.name), A.gid, A.name, soundex(B.name), B.gid, B.name, st_distance(B.the_geom,A.the_geom) * 111.325 as dist from (select name, ...
1
vote
0answers
28 views

PostgreSQL suddenly using all my disk space

Today I fired up my linux mint via my virtual computer (host is windows 8), left for 30 minutes, and when I was back, I got an error saying I was out of disk space. Yesterday I had about 20 free GB ...
0
votes
0answers
29 views

PostgreSQL becomes very slow after heavy utilization

I have built a Python web application and I am hosting it in a 2GB RAM Debian Server. My application uses PostgeSQL 9.1 with PostGIS. One of my tasks involves importing data from big csv files. ...
0
votes
0answers
10 views

Adding postgis geometry with rake task

I have a postgres table test with a geometry column: Column | Type | Modifiers ...
0
votes
0answers
11 views

How can I create a copy of postgis db from one windows machine to another

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database. In mssql it would be simple , just ...
1
vote
0answers
14 views

Install PostGIS without Internet Connection

Hi I have binaries but am working on a machine that is on a network not connected to the internet, and I need to install and run PostGIS without Stack Builder or an internet connection. I've tried ...
0
votes
1answer
63 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. ...
1
vote
0answers
76 views

Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?

Disclaimer: This may be a dumb question. This is honestly way over my head... one of the pitfalls of using Heroku for too long. I have a PostgreSQL database on Heroku. Now I'm migrating to Digital ...
1
vote
0answers
56 views

rt_raster_to_gdal: Could not load the output GDAL driver

I have already set the environment in windows SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; Even I tried by adding following in environment path- POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL But ...
0
votes
1answer
62 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 ...
1
vote
1answer
79 views

Installing PostgreSQL and PostGis on Mac OS X

I downloaded and installed PostgreSQL and PostGis from http://www.kyngchaos.com/software/postgres, I work on OS X 10.10. When I go into the terminal now and type psql it says -bash: psql: command ...
1
vote
0answers
94 views

How to optimize inefficient query in PostGIS

I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow). This query takes about ...
0
votes
1answer
144 views

How to make a single row result query to return multiple rows

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography. I also ...
0
votes
0answers
36 views

Optimizing PostGIS ST_Contains query

I've been getting some abysmal performance from this query and I've been trying to find methods for optimizing it. What might be done to it? I've thought of changing the SRID –– it's 4326 now –– and ...
0
votes
1answer
26 views

PostGIS 1.5 Error with PostgreSQL 8.4

I am trying to add PostGIS functionality to a database that I created in PostgreSQL using the following command: psql -d <db_name> -f /location/to/this/file/postgis.sql However, I receive a ...
3
votes
0answers
158 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 ...
0
votes
0answers
125 views

MongoDB to store vehicle tracking information

I am building an app similar to Uber for tracking vehicles. Since the update frequency is so high (accounting for several users), I want to know the general practices involved for making writes faster ...
1
vote
1answer
61 views

Are PostgreSQL dynamic arrays more wasteful than plain text?

Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to ...
0
votes
0answers
95 views

How to store multiple raster files in postgres / postgis efficiently

This question is partly linked with another question on GIS:stackexchange, but I believe it is has more to do with clever data management. I have a Postgresql server with Postgis extension running ...
0
votes
1answer
84 views

Trying to map postal codes to their respective cities, but running into some issues

The Problem I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now: Now, let's take Tuskegee University ...
0
votes
0answers
105 views

How to deal with finding nearby locations when given a zip code or city?

I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and ...
2
votes
2answers
35 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...
1
vote
0answers
63 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
3
votes
2answers
867 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 ...
2
votes
2answers
313 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 ...
1
vote
2answers
188 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 ...
0
votes
1answer
390 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 ...
2
votes
1answer
618 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 ...
3
votes
1answer
171 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 ...
0
votes
1answer
96 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
-1
votes
1answer
645 views

postgres: how to use subquery correctly?

I have the following query CREATE TABLE test_buffslip AS SELECT ST_INTERSECTS( (SELECT ST_Buffer(geom, 0.005) FROM segments where class = 7) ,geom) FROM segments; resulting in ...
0
votes
2answers
2k views

Difficulty with Postgis extension on Ubuntu 14.04

I have a updated Ubuntu 14.04 running postgresql 9.3 (installed using the tutorial from https://wiki.postgresql.org/wiki/Apt). When I access my database through pgadmin and go to the console and ...
1
vote
1answer
55 views

Howto do a hard upgrade to postgis-1.5 to postgis-2.0

I need to upgrade from postgis 1.5 to postgis 2.0 but i cant backup my data and restore it can someone give me a link where it is clear enough for doing this?
1
vote
1answer
118 views

PostGis getting rid of the lat long from table

This question is related to this question : Order by distance Now my tables have changed a bit. Table cafes now doesn't have latitude, longitude columns instead there are table addresses which ...
1
vote
1answer
46 views

PostgreSQL : send all rows of a table to a function

I have a table with one column (double precision numbers). I also have made a function (with double precision numbers as input and output), and I want to send every value of my table to the function, ...
3
votes
1answer
2k 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 ...
0
votes
0answers
168 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
2
votes
0answers
130 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 ...
4
votes
1answer
466 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 || ')' ), ...
2
votes
1answer
195 views

What does: ERROR: tuple offset out of range: 0 mean?

It seems (but I can't tell for sure) that after I added a postgis indexed column (USING gist) to my database last week, I have been seeing a lot of those errors: ERROR: tuple offset out of range: 0 ...
4
votes
2answers
611 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) ...
2
votes
2answers
285 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 ...
4
votes
0answers
238 views

User locking tables without privileges [closed]

This is a restatement of a question asked on the GIS Stack Exchange here. Although it has GIS-specific elements it may be be a more general PostgreSQL effect. I have a PostgreSQL 8.4.4 database ...
0
votes
0answers
332 views

PostGres- How to loop in Table to update every record in Table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update the ...
8
votes
1answer
432 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 ...
0
votes
1answer
230 views

Problems using “Excel PostgreSQL Import, Export & Convert Software” [closed]

I want to export data from Postgresql, and I tried many ways then find the tutorial of Excel PostgreSQL Import, Export & Convert Software on Youtube. I installed the software, but I could not ...
1
vote
1answer
2k views

PostGIS “could not open extension control file” when creating extension

I've followed the instructions here to install PostGIS http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt but when I went to create the extension I got the following error. I'm ...
3
votes
1answer
436 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 ...
1
vote
0answers
201 views

PostgreSQL how to create a scalable location-based query

I need suggestions because I am not good enough. I have a DB in PostgreSQL that runs on AWS (Amazon Web Services). I have a table "user_location" where there are stored the locations of each user ...