PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
0answers
58 views

PGSQL2SHP Commnad switch column type

I work with Postgis. I have table CREATE TABLE centerline ( gid serial NOT NULL, l_from integer, l_to integer, r_add_pre character varying(15), r_to integer ... } Export this table with ...
0
votes
2answers
45 views

Which function is most appropriate to select postgis geometries that have any part inside another?

I ST_Dwithin the function returns TRUE if the geometry A is completely inside B. I happen to have many polygons (representing regions) and need to know which streets are within each of these polygons. ...
0
votes
1answer
34 views

I need to test a condition within the sql in postgis.

I need to test a condition within the sql in postgis. My sql: SELECT (zoneamento3_32722.zt3), logradouros32722."NMIDEABR", logradouros32722."OBJECTID", ...
0
votes
2answers
51 views

PostgreSQL/PostGIS - group by series of values

I need to sum up linear objects' length and to group them by intervals of several years. I have one table storing my objects like this : - gid serial NOT NULL, - year INTEGER, - the_geom ...
2
votes
1answer
89 views

Centroid of Points using postgis

In my postgres database i have a set of points(lat, long) and i created the_geom index for that. Now i have to find the centroid of these points. I Google a lot and i find that for closed ...
0
votes
1answer
40 views

How can i store shape file in postgress database as a table using php?

I want to create a table from a shape file in Postgres. To do that, I can run this command in cmd: shp2pgsql -s 4326 -W LATIN1 E:\Any\THA_adm public.test123 | psql -h localhost -d DATA -U postgres -w ...
0
votes
1answer
73 views

osm2po is missing edges while importing to pgrouting

I am using osm2po for importing pbf file to postgresql but when i find route using built-in algorithms i found that some of the edges are missing so route does not match with road. Did anybody know ...
0
votes
0answers
104 views

Can't see Postgis layers added to QGIS

I'm trying to transition from an old Postgres/Postgis database to a new one. I'm working in a fresh install of Win 7 64 bit. I installed Postgresql 9.3 32 bit, then installed PostGIS 2.1.3. I created ...
0
votes
1answer
129 views

How do I avoid this osm2pgsql duplicate key error in Windows?

I am attempting to load an OSM .bz2 file into a PostGIS database using osm2pgsql but am stuck at a duplicate key value in the nodes which violates a unique constraint - any suggestions? output is ...
2
votes
1answer
203 views

POSTGIS - calculating percent area of intersection in where clause

I have a table of polygons (census block groups) in postgres. I want to tag each block group with the town (another polygon table) it mainly resides within. Is this possible? I'm thinking I'd need to ...
0
votes
1answer
33 views

managing postgresql database table using the pgadminIII sql tool

i have created a database using postgresql 9.1 and a table containing parcel information for a town council database on a windows 7 OS. i created the geometry column using the SELECT ...
0
votes
0answers
31 views

rasterize a geography polygon using postgis 2.1

I am still bit new to Postgis 2.1. I am trying to rasterize a polygon, meaning that I have a polygon of Syria and I want to create an empty raster that contains the shape and geography reference of ...
0
votes
0answers
114 views

Geocode data base of addresses using google maps api

I have a table with over a thousand addresses in postgresql, I want to make a java class that returns me the longitude and latitude of the addresses to write to the database. Does anyone have an ...
2
votes
0answers
62 views

QGIS is not working with Postgres Plus Advanced Server

We have a problem to integrate GIS software with our GIS database which is populated on Postgres Plus Advanced Server 9.2 from EnterpriseDB. We are using QGIS to maintain and analyze our GIS ...
0
votes
1answer
115 views

pgShapeLoader: how to load a shapefile in a different schema than 'public'

I'm trying to load a shapefile in POSTGIS using pgShapeLoader. It works but I can load table only in the "public" schema and i don't know how to configure, the tool to upload my shapefile in another ...
0
votes
0answers
47 views

pgrouting pgr_drivingdistance() query gives inconsistent results

So I have been working on a query(with the help of other stackexchangers :)) to get the edges in an isochrone from a particular point and then update the count_events field of those edges by some ...
1
vote
1answer
131 views

Geoserver OSM data Imported via osm2pgsql - most efficient tables to use to style roads

The Osm2pgsql importer for OSM data to Postgres results in two tables for ways, one called planet_osm_roads and a second one called planet_osm_lines The Osm2pgsql documentation states that the ...
1
vote
3answers
164 views

Dismiss invalid Polygons in PostGIS

I Have a massive Dataset from OpenStreetMap and imported it into a Postgres-Database using ogr2ogr. I got several warnings during this process because of multipolygons with too little vertices. Now I ...
1
vote
0answers
60 views

Add polygons to PostGres to be queried by Nominatim

I'm trying to build out a geocoder that also returns the correct legislative district information from this data: http://www.census.gov/geo/maps-data/data/tiger.html I've got OSM data imported into a ...
0
votes
1answer
77 views

How can I find the segment of a line that lies within a polygon using Postgis?

Given a "road", represented by a line, that crosses a "state", represented by a polygon: What is the SQL query I can use to find the segment of the road that is within the state? A concrete example. ...
0
votes
0answers
53 views

PostGIS Function both work fine

I create two function both work fine same solve, but one of them, take to much time (which one use "temporaly"). Is very important because I will run in several times the funcion. Please any one can ...
2
votes
0answers
38 views

Need help deciding on a database and datatype [closed]

I realize that these questions should probably be directed to a DBA but I feel like you might have some experience in managing the same kind of data. We have an existing system where we are storing ...
0
votes
0answers
67 views

Using postgis functions in ArcGIS Server 10.2 for Layer Definition Query

I asked the previous question Using PostGIS functions in ArcGIS Desktop for Layer Definition Query? And got an excellent answer. However this covered ArcGIS Desktop. In 10.0 it seemed that if I got ...
0
votes
0answers
46 views

Determine the list of circles (from 100K circles), that overlap with a given polygon

This question was asked on StackOverflow, came here because no response has been posted so far. Existing data structure we have polygons represented as an ordered list of lat/long coordinates and ...
0
votes
1answer
41 views

query to get predicted location after traveling on pgrouting route for some hours, postgis 2, postgresql 9.3

I am using Postgis 2.0 on Postgresql 9.3 on Ubuntu 14.04 x64. I am trying to find an efficient way to fulfill this routing request. So I am able to get the shortest route using the source and target ...
1
vote
1answer
176 views

Setting up PostGIS: Postgres can't see the PostGIS extensions?

I've just installed Postgres 9.3.4 and PostGIS 2.1 on OSX Mavericks using the very latest versions of the KyngChaos libraries. This is a new machine, and I deliberately haven't installed Postgres ...
3
votes
1answer
61 views

Rounding bug using ST_GeomFromGeojson()

Let's say I have the following geographical point represented in GeoJSON: {"type":"Point","coordinates":[-109.0,35.292506]} When I run the following conversion, things work as expected: SELECT ...
1
vote
1answer
143 views

storing date range in PostgreSQL/PostGIS for time parameter in WMS/WFS

Using GeoServer I want to deliver some objects/features with WFS and WMS. The objects and their attributes have a date range during which they are valid. Say, we have street lamps and each street lamp ...
1
vote
0answers
36 views

Import raster to postgis and add a foreign key constraint

I use raster2pgsql to import raster files into a table with this command line: C:\Program Files\PostgreSQL\9.3\bin>raster2pgsql -s 4326 -M -F -a c:\temp\mun.tif public.basqui_raster | psql -U ...
3
votes
1answer
126 views

How to render overlapping bus trips as one route from OSM?

I would like to create bus map for my city from OSM data. I imported data into Postgresql with osm2psql and I can render routes with Mapnik. But the problem is that same route ref is shown multiple ...
0
votes
0answers
35 views

Postgresql (soln would be basic SQL I think): ORDER by distance to a point and return those distances in array pairs w/ the full records

so I know there have been some questions asked about how to calculate distance using postgis and how to order results by their distance from a respective point. I'm not very talented at SQL (at all) ...
0
votes
1answer
31 views

PGrouting encapsulated queries error [quotes problem]

I'm facing a quote problem on a pgrouting query. What I'm thinking is wrong is that my query only works if I use one, then four, then eight quotes on encapsulated queries. Is there a way to simplify ...
1
vote
2answers
101 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
1
vote
1answer
49 views

PostGis line geometry type

I am using postgresql9.3 and postgis2.1.I imported a shape file into my database. In that the_geom datatype is "LINESTRING" . Now i need a query to find the lines within a given radius for a ...
1
vote
1answer
289 views

Is it possible to get a binary geometry from PostGIS?

PostgreSQL (via C# Npgsql driver) returns geometry column (PostGIS) like a hex string (EWKB as I understand) for this query: SELECT ST_Transform(geometry, 4326) FROM myGeometries; Result (for ...
1
vote
0answers
121 views

Why does QGIS crash when my PostGIS table has a primary key?

Configuration: PostGIS Library: 2.0.3 GEOS: 3.3.8-CAPI-1.7.8 Proj: Rel. 4.8.0, 6 March 2012 PostgreSQL 9.2.2, compiled by VC++ build 1600, 64 bit, running on Win2012 server 64 bit QGIS 2.2.0 - 32 ...
1
vote
1answer
30 views

Access SQL database in postgres for routing

i am trying to access sql database in pgadminIII. Using postgres_fdw I am trying to connect. But I am struck in the middle..Following are the steps I followed CREATE EXTENSION postgres_fdw; CREATE ...
0
votes
1answer
215 views

Error importing shapefile into postgreSQL using shp2psql

I've tried this a few different ways and keep getting the same error. I have a shapefile currently in WGS84 UTM Zone 15 which is SRID 32615. I used the ArcMap multipart to singlepart tool to create a ...
0
votes
0answers
62 views

Batch join non-spatial csv to shape files

I have 173 point shape files from soil testing and 173 non spatial csv files with lab ressults (one csv for each shape file) that need joining. The files are located in a library with folders named ...
1
vote
1answer
160 views

How to calculate the track length of GPX files with PostGIS?

I am trying to calculate the length of a GPX track. The original GPS data looks like this: <?xml version='1.0' encoding='UTF-8' standalone='yes' ?> <gpx ...
2
votes
1answer
113 views

Using PostGIS functions in ArcGIS Desktop for Layer Definition Query?

I am connecting ArcGIS Desktop 10.2 to Postgis 2.0.3 with PostgreSQL 9.2.4. No SDE. I can load data with no problems and access the data in ArcMap. However I can't seem to use PostGIS functions within ...
-1
votes
1answer
157 views

how to build and install pgrouting-2.0 on postgresql-9.3 on ubutntu 12.04 Lts precise?

I try to build following instructions from this link https://github.com/pgRouting/pgrouting after downloading from the github. I have done the cmake command and there is my error output: ...
1
vote
1answer
57 views

Install pgRouting on Postgres 9.1 and Win 32

I referred some documents on GIS-stackexchange and over internet regarding installation of pgRouting on Postgres v9.1 and windows 32. I am not getting the correct version of pgRouting to install. Can ...
14
votes
1answer
354 views

How to query OSM for all beaches facing west? - QGIS preferred

I want to identify all beaches on this planet (or a given subset of it) that are facing westwards (+/- 20-30 degrees). My idea is to use OSM as the data source. Extracting all polygons that have the ...
0
votes
2answers
417 views

Update database via osmosis and osm2pgsql too slow

I got a box with the current hardware config: CPU: Intel Xeon E3-1220 V2 3.1 GHz RAM: 10gb Hard Disk: Western Digital WD10EZRX SATA Hard Drive OS: Centos 6.5 64 bit DB: PostgreSQL 9.3.2 and ...
2
votes
1answer
92 views

PostgreSQL : how do I make this sum [closed]

I have this huge table with all the addresses of my country in it. Every house is an object, and there is a column for the streetname (or street ID) and a column for the housenumber. I want to make a ...
0
votes
2answers
78 views

QGIS Connection PostGIS and shapefile

I'm working on QGIS with a database created in PostGIS and a shapefile. Inside the database, I have geometry type data about location of some points, instead on the shapefile there some selected ...
2
votes
1answer
69 views

Get list of all tables in CartoDB account with SQL statement?

How can I get a list of all available tables in my own authenticated CartoDB account with a SQL statement? This returns null: SELECT table_name as t FROM information_schema.tables WHERE table_schema ...
2
votes
2answers
139 views

How to get the data type of each column from a postgis table

I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this: column_name | ...
4
votes
0answers
59 views

Compute visibility graph on sphere

I have a PostGIS table with some polygons (stored using the geography data type). They represent regions on a spherical earth. For each pair of vertices chosen from among all the polygons, I want to ...