1
vote
1answer
32 views

Postgis create polygon extent/convex hull from spatial table

Please help, I have a polygon layer representing building footprints. I want to create new view in postgis that will contain rectangle representing bounding box and also another view that will have ...
1
vote
1answer
44 views

SQL-query should return GeoJSON FeatureCollection

I'm trying to return a GeoJSON FeatureCollection object from a SQL-query with PostgreSQL 9.3 and PostGIS 2.1. This is my query so far: SELECT row_to_json(fc) AS p FROM (SELECT 'FeatureCollection' ...
1
vote
1answer
68 views

PostgreSQL/PostGIS: create spatial view

I have a polygon layer and a point layer. 1) I wrote a spatial query to count how many points fall in each polygon: SELECT polR.codeR, count(pntAZ.geom) as tot_pntAZ FROM boundaryR as polR LEFT ...
0
votes
1answer
31 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
1answer
28 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
28 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
22 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
71 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 ...
2
votes
1answer
54 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
84 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 | ...
1
vote
1answer
25 views

why is the CDB_QuantileBins function not found in cartoDB?

What is wrong with the following SQL query on my data? I found pretty clear documentation (1,2) that the function is supposed to work. SELECT CDB_QuantileBins(array_agg(numeric_column_of_mine), 10) ...
0
votes
1answer
56 views

how to run a PostgreSQL query on CartoDB tables?

I want to run a VACUUM FULL on my table, but I don't think it's working. Simply sandorl.cartodb.com/api/v2/sql?q=VACUUM&api_key={MYKEY} results in long output, apparently only repeating for ...
-1
votes
1answer
132 views

Shortest Path Dijkstra

I would like to calculate the shortest path with dijkstra algorithm. sample query SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra(' SELECT gid AS id, ...
0
votes
1answer
81 views

How to cut/split multipolygon with blade (linestring) into two pieces using PostGIS? [closed]

I struggle to do split operation in postgis I tried to build a query but Im always fail. Id like to cut a multipoligon with a blade (a linestring) into two pieces. These two objects have specific ...
3
votes
1answer
333 views

PostGIS intersection function

I have a problem with intersection function. I built my query following http://manual.linfiniti.com/en/postgis/geometry.html, 5.7 section. I made my clip object, a red linestring frame: But after ...
4
votes
2answers
435 views

SQL Syntax to extract (convert) Strings with Degrees Minutes to Decimal Degrees

I have a PostgreSQL/PostGIS database with a two string fields containing Lat and Long coordinates in degrees and decimal minutes, like this 45° 24.5' N 3° 43.01' E. How can I convert them into ...
4
votes
2answers
675 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
200 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, ...
2
votes
2answers
81 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
1answer
572 views

Postgresql Trigger to insert records in my table from another one

I spent few days trying to resolve this question but it was impossible. I have two tables: Table1. CREATE TABLE point ( id_point integer NOT NULL DEFAULT, layer text, elevation numeric, ...
0
votes
1answer
292 views

Postgis: find geometry within multiple polygons

I've got two tables with geometry in it. Table 1:items holds items with a point geometry; while the second table property_zones holds polygons. The Items: CREATE TABLE public.items ( id integer ...
1
vote
0answers
40 views

Selecting a bounded area within a raster

I am new to postgresql. I have imported a 256kb sample raster (.tiff) into postgres with tilesize 100x100. Now I need to select the area (rectangle) from the center of the image using sql query. Then ...
2
votes
1answer
81 views

Tiger geocoder / postgis - get county from point

Looking for some help with getting the county for a lat / lon. I have postgis / tiger geocoder installed and can reverse look up a point and get the addresses. I would like to get the county the ...
1
vote
1answer
114 views

Three table postgres sql join query

I'm having trouble getting my SQL query to work with three tables. I'm new to SQL to I've already spent considerable time trying to understand the documentation and making some sense of it. I'm using ...
0
votes
1answer
783 views

Building SQL statement in javascript and casting variables as strings

I'm working with Postgres 8.3/PostGIS which is storing some polygons. I'm writing a javascript function which will take in a parameter of a watershed id from another function. The javascript will ...
1
vote
2answers
563 views

How to connect Dotspatial with PostGIS/PosgresSQL database and perform SQL function

I am currently doing a project that needs a GIS front-end interface to visualize geographical objects (road networks), and a back-end geo-spatial database to implement network analysis (SQL based). ...
0
votes
1answer
83 views

pgrouting shortest_path return longest_path

I've got troubles with shortest path function from pgrouting. I try to calculate the shortest path between an address and a bus stop. I first found the nearest road points for the address and the bus ...
2
votes
1answer
258 views

PostgreSQL, find points near points

I'm going to have a database with a 'people' table and a 'shops' table. I'd like to find all people within 500m of a shop, and details from both tables. I'm used to working with mySQL, but I'm ...
6
votes
2answers
597 views

What are benefits of using ArcGIS with PostgreSQL over simply ArcSDE?

I understand that in order to benefit from ArcGIS spatial datatypes (ST_GEOMETRY, versioned geodatabases etc.) ArcSDE needs to be installed - and with the 10.1 client it is automatically installed ...
0
votes
2answers
890 views

Cannot insert simple geometry data

I installed PostgreSQL 9.2.4 and PostGIS 2.0.3 on my Windows 8 machine. Then I created a table using phpPgAdmin and added a geometry column using the following statement. SELECT ...
2
votes
1answer
183 views

SQL Command to convert pgRouting result to GeoJSON

My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to ...
0
votes
1answer
331 views

ST_Box2D in PostGIS 2.0

I am getting an error trying to create a bounding box of a geometry using ST_Box2D(). Is the function deprecated? If so, what is the equivalent function? SELECT ST_Box2D(ST_GeomFromText('LINESTRING(1 ...
1
vote
2answers
330 views

Function for Selecting Points in a Polygon

I've been banging on this for a few hours, but since I'm relatively new to PostgreSQL and PostGIS, I can't find the solution. I'm trying to create a stored procedure (function) which will return all ...
3
votes
1answer
186 views

Given a lat/long point, determine if it's land or water?

I have a PostGIS-enabled PostgreSQL 9.1 installation with the 10M physical dataset from Natural Earth imported. It's been converted to 900913, and in QGIS I can visualize the data. So the plumbing's ...
3
votes
2answers
154 views

Traversing a PostGIS table tree

I'm developing a geospatial db for a company that operates at a number of large facilities. Its primary purpose is to help staff locate themselves and equipment within each facilities' multi-storey ...
4
votes
2answers
321 views

PostGIS stored procedures

I am wondering if there are open source applications that make extensive uses of the special functions (the ST_* stored procedures) provided by PostGIS? I found many apps that support storing map data ...
1
vote
0answers
209 views

SELECT DISTINCT from table in Geoserver SQL view [closed]

The postgis table I have has the following columns, id | name | style_id | unique_id | time_stamp | the_geom(polygon) and logically the unique constraint (not currently imposed in the db) lies with ...
4
votes
3answers
1k views

How to join two tables using date, time, lat and lon as common field?

I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. ...
1
vote
0answers
48 views

given sets of vehicle passing on each edge, how to get most used vehicle to avoid transition?

I have two tables: one, the resulting table from converting osm.xml via osm2po so I can use pgRouting, and another one that contains information on what edges each public vehicle passes through ...
6
votes
3answers
203 views

Are there any open source Python modules compatible with arcpy that will allow complex SQL queries?

I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count ...
6
votes
1answer
318 views

Join based on maximum overlap in PostGIS/PostGresQL?

I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
6
votes
0answers
497 views

Getting ST_Difference between two different tables in postgis [closed]

One table has a series of grid squares in it, and the other has millions of voronoi polygons in it. I need to create a table that has the difference between the two of the them so that I effectively ...
3
votes
1answer
564 views

raster2pgsql: Strange error when creating a table

Hi GIS Stackexchange people, When I try to load a asc file into my psql setup, I get a strange error message. I have created the sql input file via raster2pgsql -s 4236 -I -C -M gis_data.asc -F ...
1
vote
0answers
1k views

How to populate data from postgresql using dynamic php\html listing

In the form I created, I need two fields to be dynamically populated from the database while the other fields will be manually entered and push to the database. I have written a script that is able ...
2
votes
1answer
153 views

How to create a network with all the track points

I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database. Now I have a table which holds all the track points of these ...
2
votes
1answer
1k views

How do I format SQL queries for use in Arcpy?

I have to make an SQL query on a column having not adequat name "type_borne_id", I couldn't make SQL queries using arcpy.Select_analysis neither selectbyattribute, but when I use select_analysis ...
1
vote
0answers
66 views

Database has duplicate values after running pgrouting

I am a newbie with postgresql and sql statements so maybe it is a trivial question, I'm sorry for that. I am working on a postgis database and try to use the pgrouting function. My goal is to store ...
4
votes
2answers
1k views

Publish SQL Views from Postgres/Postgis to Geoserver

I've trying and reading how to publish a Postgres/Postgis View in Geoserver. I know that I can create an SQL View in Geoserver, but I'd like to define the View in Postgres, not in Geoserver. Does ...
3
votes
1answer
442 views

Duplicate ObjectID in ArcSDE Table

After making a check in from an arcpad AXF file, i remark that there's duplicate object ID records , this Arcsde pgsql table is versioned and edited by Axf checkins and a .net engine application. the ...
2
votes
1answer
240 views

Direct DBMS sql queries and ArcSDE geodatabase

i want to make updates from Arcobjects on several tables stored on a ArcSDE 10.1 versionned geodatabase based on Postgresql 9.0.5 , none of these update require spatial data , and making these updates ...