The tag has no usage guidance.

learn more… | top users | synonyms

2
votes
2answers
59 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
1answer
68 views

How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent....
1
vote
1answer
78 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?
0
votes
1answer
21 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
0
votes
1answer
31 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...
0
votes
1answer
69 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. (...
-2
votes
1answer
2k 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 ERROR:...
4
votes
0answers
327 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 ...
3
votes
0answers
49 views

Postgresql Filter Slows Query

I've got a query like this: SELECT count(*) FROM acs2014_5yr.geoheader as g JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract) JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id) ...
3
votes
0answers
67 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....
3
votes
0answers
220 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
0answers
55 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 ...
2
votes
0answers
277 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...
1
vote
0answers
22 views

Add Shapefile loader plugin to PgAdminPortable

I'm using PgAdminPortable and I'd like to enable « PostGIS Shapefile and DBF loader » plugin. The issue is that the files are not structured as in a PostgreSQL complete install (see below). I have ...
1
vote
0answers
51 views

The definitive fastest radius search query with Postgis 2.0

I come from mysql world. I read the manual and saw some examples, after that, I'm not able to say witch method is the fastest for a radius search. Nobody seems to agrees. My simple case: I need to ...
1
vote
0answers
25 views

How can I connect MapGuide Maestro to PostgreSQL/PostGIS?

I am trying to connect my database PostgreSQL/PostGIS to MapGuide Maestro but it sends me an error message: Do you have some idea about this or can you help me about how I can connect it with ...
1
vote
0answers
25 views

PostgreSQL: Improve system performance, KNN modification with GiST Index

In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on i5-...
1
vote
0answers
34 views

PostGIS radius search performance

In my Postgres database, there is a listing table, inside that table, I have a column called geo_point, which stores latitude and longitude information. The column type is geometry(Point,4326). I ...
1
vote
0answers
13 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
0answers
165 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 (...
1
vote
0answers
112 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
354 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
147 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 ...
1
vote
0answers
164 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 ...
1
vote
0answers
82 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 | ...
1
vote
0answers
261 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 ...
1
vote
0answers
294 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 ...
0
votes
0answers
4 views

postgres: How to skip the fist and the last row of a queryset in a for-loop?

In a function I'm looping over the all rows a select statement returns. I want to skip the fist and the last row. How could I do this? ... FOR arow IN SELECT (dp).path[1] As index, ...
0
votes
0answers
10 views

how to enable shp2psql-gui in pgadmin3 with postgresql-9.5 on centos

I have installed postgresql-9.5 on centos with postgis2_95 extension. Now I want the shp2psql-gui plugin tobe enable in my pgadmin3. Please help on this as I am unable to enable it though I have ...
0
votes
0answers
10 views

postGIS out-of-db still has error when environment file is updated

I'm running postGIS 2.1 with a PostgreSQL 9.1.21 and am trying to get rasters back out of a database and am running into the error: ERROR: rt_raster_load_offline_data: Access to offline bands ...
0
votes
0answers
50 views

Issues with setting a global setting in PostgreSQL 9.5/PostGIS2.2

I am trying to set a global setting/GUC variable postgis.gdal_enabled_drivers from the PostGIS extension for a while. I am a non-admin user but can ask admin to change settings if needed. But I tried ...
0
votes
0answers
10 views

Setting Column Privileges in Postgis Fails to Commit Changes in a Read/Write Column

Background information. I'm running Postgresql 9.5 and Postgis 2.2 on a Windows 7 platform. Read-only privileges applied to a table. One column in the table receives read/write privileges. Editing ...
0
votes
0answers
16 views

trying to find closest road segment to a node I have

I am trying to find the closest road segment to a point that I have and return an attribute from the closest segment to the given points. I know in POSTGIS I need to use ST_ClosestPoint, St_distance ...
0
votes
0answers
45 views

accessing postgis extension capabilities

The context is pertinent as it demonstrates behaviour different than other (more manual) successful attempts. Deploying a rails application with postgres and postgis extension to AWS EC2 instance, ...
0
votes
0answers
26 views

postGIS dump size seems strange

I have this table on postGIS 2.1.5 (postgres 9.4.3). A pg_relation_size shows a size of 300MB. However when I try to dump the schema containing this table (and only this table), I end up with a 27GB ...
0
votes
0answers
90 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
39 views

Adding postgis geometry with rake task

I have a postgres table test with a geometry column: Column | Type | Modifiers --------------...
0
votes
0answers
236 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 ...
0
votes
0answers
161 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 ...
0
votes
0answers
541 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 ...
0
votes
0answers
3k views

Cannot run pgAdmin from the terminal: No such file libwx_gtk2u

(Probably irrelevant) I'm using Puppet to setup/manage a class of computers that are equipped with PostgreSQL and PostGIS, and this implies that each computer goes through an unattended install: ...