PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

6
votes
1answer
816 views

POSTGIS SQL - How do I remove (defined) sliver polygons

I would like assistance in writing SQL (for POSTGIS in postgresql) to remove sliver polygons (including long narrow slivers and some small irregular polygons that were erroneous 'gaps' in the original ...
6
votes
1answer
593 views

Spatial database containing coordinates for stars in the sky?

My goal is to map the Skydome ( stars in the sky projected in earth surface ) using GIS. Has anyone done this before? I can't find any project close to this. What I just need to know is if there is ...
1
vote
1answer
184 views

Scaling CartoDB to multiple machines

I'm interested to scale CartoDB installation to multiple machines to distribute computational resources. We start using torque with some big tables (more than 5 to 10M points) and the processing for ...
5
votes
1answer
2k views

Copy a spatial table from one PostGIS database to another PostGIS database

I have two PostGIS databases on two different servers. The first server is PostGIS 2.0 and the second server is PostGIS 1.5. On the PostGIS 2.0 I have a table "lakes" with MULTIPOLYGON geometry ...
3
votes
1answer
345 views

Computing radius of gyration in PostGIS

The radius of gyration is a measure of the standard deviation of distances between points (and their center of mass), i.e., how frequently and how far points are moving. A formal definition is: I ...
2
votes
0answers
362 views

How to best intersect vector data with raster data?

I have Open Street Map [OSM] data imported so that I can access the geometry of buildings. I have also loaded SRTM digital height data from NASA from a GeoTIFF raster. I have projected the raster into ...
1
vote
2answers
1k views

Select points from a table that exist in a custom polygon

We have a property search site where the user draws a polygon on a map and then the app displays all properties that fall within the polygon. We have properties stored within a Postgres table that ...
6
votes
2answers
865 views

How to check if points are within a circular sector using PostGIS?

I want to get the stars within the black region as shown above. To find this I have the centre of circle (lat, long), radius and the angle of region (45 degree). I am new to postgres. I worked on ...
1
vote
1answer
890 views

Why does reading shapefile from PostgreSQL database give Error reading OID from table …?

I'm using arcgis 10.1 and i have a connection to a PostgreSQL 9.3 database. When i try to load a shapefile using the add query layer I get an error when opening the attribute table. The error says ...
2
votes
3answers
459 views

Why are some points in my PostGIS layer not displayed?

I have PostGIS table with some points and various attributes. Yesterday I added some more points to the table and now weird things are happening when I try and display layers built from these points. ...
3
votes
2answers
407 views

How to handle PostgreSql 1:m relationship data by using “Data-Driven Input Mask” QGIS plugin?

I have 1:m relationship tables in my PostgreSql. I like to show the main table attributes as well as the relational table attributes (like grid rows) in QGIS attribute-edit-form. What are the entries ...
0
votes
2answers
183 views

Osmosis, Save to different schema

Does anyone know how to use osmosis to save to a different schema? I used the pgsnapshot_schema_0.6.sql to create a blank data table, but it always creates it in the public schema. I need to create ...
2
votes
2answers
894 views

Can I achieve one-to-many relationship in QGIS custom forms?

My PostgreSQL database has ElectricLine geometry table which has one-to-many relationship with ElectricLineMaterial non-geometry (attribute only) table. This PostgreSQL table added as layer in QGIS ...
4
votes
2answers
624 views

How to optimize a ST_Within query to count lightning occurrences inside a country?

I just started learning PostGIS, and right now i'm trying to count the number of lightning strikes in my country (Brazil in this case). I have the following tables: earth_feed --> This holds the ...
0
votes
2answers
276 views

How to add other column into pgrouting query result in pgAdmin output pane?

I'm working on my final research project now and I'm a new user in pgrouting. Is there any chances to add another column name to display in the output pane? I'd like to add road name column so that I ...
0
votes
1answer
503 views

Problems while installing Postgres [closed]

I was trying to install Postgres several times, but it didn't work out. I am using Mac and I tried to install Postgress.app, I got problems with database connection. I decided to install Postgress ...
2
votes
2answers
918 views

How to find the row that has a shape file containing a lat long point in my PostGIS geometry column

I am fairly new to PostGis and I'm trying to return a row in my table that has the shape file containing a lat long point I pass in via st_contains. Below is a query I wrote myself that brings back 0 ...
1
vote
2answers
678 views

Problem Importing shapefile into postgresql with simple geometries

I have a Line Type shapefiles to be imported to postgis database on my postgresql, I'm using the SHP loader, I checked the "Generate simple geometries instead of MULTI Geometries" options so that the ...
0
votes
1answer
205 views

pg_escape_string() equivalent?

A client is hosting one of my maps on a server that does NOT have a postgres module installed, and will not install it (nor anything else... ) . We have been told also by the site owner that we can't ...
1
vote
0answers
58 views

Create a single line from a resultset of line segments

How can I create a single line using the line segments geometry found in each row in resultset? I followed this tutorial: http://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ I ...
5
votes
3answers
6k views

What's the fastest way to do a bounding box query in PostGIS

I have a Postgres table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405). Supposing there's a geospatial index on that field, what's the most ...
0
votes
0answers
284 views

Mapscript. Converting pixel coordinate to latitude longitude for postgis

I have a map created with mapscript. It is a 2D (PNG format) that's being shown on a web browser. I want to get the lat/long coordinates whenever the user click on the image, and then save it in my ...
1
vote
1answer
211 views

Creating a new time column in PostGIS / CartoDB

I received a CSV file that has year (just two digits, '70'), month (as 'JAN'), day and time (as '0310') as separate columns. Bringing in to CartoDB / PostGIS they all appear as strings. I'd like to ...
3
votes
1answer
921 views

Access Shapefiles from PostgreSQL/Postgis in R

I successfully setup a connection to my PostgreSQL/PostGIS database on Ubuntu Server from my Laptop (Ubuntu 12.04). Therefore I installed RODBC in R to setup a connection and I made a correct entry ...
1
vote
1answer
209 views

Import OSM data into ArcSDE using osm2pgsql

I am trying to import OpenStreetMap (osm) data into a PostGRES database using OSM2PGSQL, and then I need to convert that data so I can use it in ArcSDE/ArcGIS Desktop 10.2. Here is what I do: ...
9
votes
2answers
4k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
1
vote
1answer
769 views

How to install pgrouting on Postgresql 9.2 located at /opt/PostgreSQL/9.2/ on Ubuntu Server 12.10?

I have installed Postgresql 9.2 on Ubuntu Server 12.10 using one-click installer from the EnterpriseDB. Then using the StackBuilder I installed PostGIS. Now I also want to install the pgrouting ...
0
votes
1answer
38 views

Postgres Data insertation [closed]

I have a table with 4 column. There are thousands of data. The column names are serial || name || X || Y From 500 I made a mistake and put the X data into Y field and Y data into X field. So can I ...
5
votes
3answers
467 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 ...
2
votes
1answer
5k views

Trouble with INSERT in PostgreSQL 9.1

I am trying to execute the following: INSERT INTO lithic (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom) SELECT (cp_east, cp_north, ...
4
votes
1answer
175 views

Insertion of a raster geotiff in PostgreSQL database

I am new in PostgreSQL and psql. What is the method to insert a raster geotiff in a postgreSQL database using command psql? What is the best format? Someone indicates to save a raster image as BLOB ...
2
votes
2answers
184 views

shp2pgsql mangles date field — quotes data as if it were char or varchar

I'm attempting to import a shapefile into postgis via shp2pgsql. Among the fields in the shapefile's .dbf file is a date field with format yyyymmdd, which is a valid date format for postgresql. ...
0
votes
2answers
76 views

Select area which is at least x meters away from other geometries

In my table I have the build-up area from a region as a big number of polygons. urban_area: id | name | geometry(polygon) I was wondering if and how I would be able to find the area within a ...
2
votes
1answer
312 views

Why is ArcMap leaving connections “<IDLE> in transaction”?

The TL;DR version: When you use ArcMap Query Layers to access a PostgreSQL table, ArcMap's database connections are almost always sitting idle in unclosed transactions. Why would this be? I have ...
0
votes
0answers
227 views

error occured when importing file on cartodb local host

Logged in as development user. Not able to import a .zip or a csv file. Ruby console has this error log 1.9.2p320 :004 > DataImport[1].log 2014-01-07T05:44:44Z || Using database connection with ...
8
votes
2answers
3k views

Install PostGIS and TIGER Data in Ubuntu 12.04

Could someone write a brief albeit dumbed down idiot's guide to installing postgis and loading national Tiger data on ubuntu? I've tried a few guides, namely, ...
0
votes
1answer
120 views

INSERT INTO Error

Hey guys I am trying to get the results of a query into a column in my table. The error I received is this ERROR: Quota exceeded by 16KB. I am using cartodb API, INSERT INTO address (distance) ...
1
vote
0answers
564 views

postgresql 8.4 and postgis 1.5 error: type modifier is not allowed for type 'geometry'

I would like to generate a buffer (within a limit of 3km ) where the user clicked point. In pgAdmin, select my database and open a query editor and create 2 PostGIS functions (UTM Zone Function and ...
1
vote
0answers
1k views

Creating a spatial database using EXTENSIONS: error loading legacy.sql

I am using Postgres 9.3 and PostGIS 2.1, installed in a Docker container via RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list RUN apt-get -y ...
0
votes
2answers
519 views

Building CartoDB from source using updated dependencies on Ubuntu 13.10

I am attempting to build CartoDB from source on an Amazon VM for the first time in my life. For purposes of sanity, I want to use the same instance/database of postgres for both CartoDB and all of my ...
2
votes
1answer
458 views

creating a st_contains query with geographies not geometries

I'm trying to find all the geography points that are contained in a geography polygon, let say all the traffic lights within a given city or neighborhoods, how can i convert this working st_intersect ...
1
vote
1answer
99 views

python scripts for accessing info from a postgresql database in case of a qgis application?

I am beginner and I need to know if I could access a postgresql database from python script made for a qgis application
1
vote
1answer
261 views

Ogr2ogr - shape to postgis - is there a way to use and preserve an existing primary key?

I've been struggling with this for a while, tried a few different ideas inspired by these pages: http://www.gdal.org/ogr2ogr.html http://www.gdal.org/ogr/drv_pg.html ...
0
votes
1answer
294 views

Cast record type to geometry type

How can I cast type record /subquery/ to geometry type? My example: select geom from (select (ST_Simplify..... ) as geom and result is type record, but I need to convert to geometry.
3
votes
1answer
744 views

How can I decrease size of geodatabase “Nominatim”?

I downloaded Nominatim http://wiki.openstreetmap.org/wiki/Nominatim/Installation. The database size is more than 400 GB. The speed of geocoding is very slow. I want to increase it. For example, I ...
0
votes
1answer
689 views

How to install pgrouting 2.x on ubuntu 12.04 LTS

I try to install pgrouting 2.0 on ubuntu 12.04 LTS with postgresql 9.2 I have ppa in the system by sudo add-apt-repository ppa:gwibber-daily/ppa answer: `You are about to add the following PPA to ...
1
vote
2answers
672 views

osm2pgsql error when import pbf for Nominatim DB

Good day to all, Am getting the following error when running utils/setup.php in nominatim 2.1: Reading in file: /storage/Library/geodata/africa-latest.osm.pbf Processing: Node(54854k 62.1k/s) ...
2
votes
0answers
179 views

Data loss after compressing geodatabase in ArcSDE

I suffer sometimes from data loss after compressing ArcSDE geodatabase, this geodatabase contains versioned feature classes, so the compressing script reconciles and compress the geodatabase: # ...
0
votes
2answers
386 views

GeoAlchemy pg_function.geojson cannot be decoded with json.loads

I asked this quesiton over on StackOverflow and thought it might make more sense to post it here given it's GIS nature. Given this function: features being a list of SQLAlchemy objects template ...
3
votes
1answer
819 views

Retrieve geographic coordinates from OSM imported data

We have just finished importing OSM data into our PostGIS (Postgresql) database. And we are having difficulty understanding the geometry of various shapes imported. We run a query to retrieve the ...