Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

learn more… | top users | synonyms

1
vote
1answer
66 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
63 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
0answers
47 views

Is there a limitation in quantity of joined tables using spatialite views?

I've one point layer in qgis and several non spatial tables in a spatialite database. Now I want to create a view as you can see in the following: CREATE VIEW testansicht10 AS SELECT ...
0
votes
1answer
40 views

Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0

I'm having issues getting the pgrouting workshop to work on Postgresql 9.2/PostGIS 2.11 and pgrouting 2.0 on windows. How can I rewrite the following php/sql code and make it compatible with my ...
0
votes
1answer
56 views

Create Area or Polygon based upon a Series of points

i have a large list of points that i would like to merge to create an Polygon based upon an attribute. Essentially an area that encompasses all points. x | y | attr ____________________ 1 | ...
3
votes
1answer
99 views

ArcMAP SQL Statement Iterate Feature Selection 'IN' 'BY' 'IS' - escape character

We have a problem when selecting Features from a Feature Class in ArcMAP 10.2 using the Iterate Feature Selection tool in Model builder Our dataset has a Attribute Field with two character codes like ...
3
votes
1answer
121 views

Edit SQL Server table (not registered with geodatabase) with arcpy

I am trying to update some rows in a SQL Server database table (not registered with geodatabase) with arcpy.da.UpdateCursor. However, when running the deleteRow(), I get the error return without ...
1
vote
1answer
43 views

Using Like on Join Field

Ok, so this has probably been asked by someone else out there somewhere but I can't find it via google or this stack, so... I've got two tables from an SDE database (SQLServer Express 2008 r2) that ...
0
votes
0answers
15 views

Multiple cursors in sql for SDO_DIFFERNCE

This is a follow up to a previous question I had regarding sdo_difference. Now in this case I am trying to use cursors so that I can do it for all records at once.In the below code I select a list ...
1
vote
2answers
82 views

sqlite version error with ogr2ogr

I am trying to randomly select some points from a shape file using ogr2ogr and an SQL query via sqlite but it seems to baulk at the sqlite version: ogr2ogr -sql "SELECT * FROM probe_2004to2012 WHERE ...
0
votes
1answer
29 views

Wrong output wth group by and aggregate function in spatialite

I have a grid and for every cell I want to set the column "landuse" according to the largest intersecting polygon. SELECT grid.PKUID, landuse.landuse, ...
0
votes
0answers
69 views

Date Parameter in model builder - ArcMap

Basically I'm building a model where the user has to select the data from a drop down list and the result is executed. I have a problem when trying to pass a date as a parameter. Below you can find ...
0
votes
0answers
57 views

Trying to load ThinkGeo shapefiles into SQL

I am new to GIS, but have a work project that I need to integrate CoastLine geometry data in to a report. I downloaded some of ThinkGeo's OpenStreetMap shapefiles and have tried to load them using ...
2
votes
2answers
191 views

CartoDB- Passing selected features bounds to map center parameter (Zoom to selected features)

Looking to build a light-weight viewer for some data we have hosted on CartoDB. I'm looking to have a user input their address into an input, pass a query to CartoDB and have the map zoom/pan to the ...
1
vote
1answer
264 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
45 views

Could SQL performance be increased by storing rotations of the data?

I'm working on a project where this kind of SQL Psuedocode would apply: SELECT * FROM objects WHERE (distance My inputs are latitude and longitude, and my database holds latitude and longitude. ...
3
votes
1answer
52 views

New attribute creation using ogr and regex

I've got a folder full of dxf files with elevation values included as part of a long text attribute. I've written a regex expression to extract the elevation value, and would like to add it as a new ...
0
votes
1answer
167 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 ...
4
votes
1answer
132 views

How to query nearest date in ArcGIS Server REST?

How to query nearest date (today) in ArcGIS Server REST? Table: ID NAME DATA 1 test 2013-06-01 13:00:00 2 test1 2013-06-01 16:00:00 3 test2 ...
5
votes
1answer
141 views

ogr2ogr selecting features by attributes

I am writing a shapefile after some processing; however, in the final step I need to do some selection by attributes from the the shapefile. I am using the command in a shell with the aim of using it ...
4
votes
2answers
100 views

Maximum length of WHERE clause with IN statement

I need to get all features from feature class where their field value matches to input array. To achive this one may set IQueryFilter.WhereClause property similar to 'FIELD_NAME IN (array[0], ...
2
votes
1answer
156 views

Buffer based on SQL expression

Is there any way to optimize this code? It creates two buffer feature classes based on two different fields in the shapefile attribute table import arcpy from arcpy import env ## Set Workspace ...
1
vote
0answers
37 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 ...
1
vote
0answers
94 views

How can I retrieve/query data from shape file for forms/grid in website?

I've made a site using openlayers and geoserver. currently im serving WMS layers from geoserver (shape files). I want to add in my site a search form that the user can search for data (which I have in ...
3
votes
1answer
1k views

What expressions for QGIS field-calculator?

I do not understand what language I have to use in field-calculator and labels on QGIS: SQL or Python? And where I can find the possible script/expressions to use? in the image you can see the ...
2
votes
2answers
256 views

What is faster for geoprocessing (intersect) big shapefiles?

I have 200 Mb shapefiles of polylines downloaded from OSM. I've been working with QGIS 2.0 in Ubuntu 64 Bit 8 Gb RAM to make some geoprocessing tasks and it's taking 2 days for processing. Once I've ...
1
vote
3answers
207 views

Differences between SQL dialects in QGIS vs ArcView vs Oracle

I'm kinda new with SQL queries and there is something I don't get: A SQL query in QGIS is written differently than the same query in ArcView and will probably also be written differently if I want to ...
0
votes
0answers
104 views

Geoservices REST JSON to SQL Server Geometry using Python

I'd like to hit a Geoservices REST endpoint (below) to return json and push this into a SQL Server Geometry table using Python. Ok so I can achieve this via ogr using below via command line: ...
2
votes
1answer
341 views

How to use GROUP BY with arcpy.da.SearchCursor in file geodatabase?

fc = "C:\Users\AuxS3-4\Documents\ArcGIS/Default.gdb\Malla" fields = ('OBJECTID','Sentido','Carriles','idMalla') sqlclause=(None, "GROUP BY Sentido") cursor = arcpy.da.SearchCursor(fc, ...
2
votes
1answer
80 views

How to split polygons with another polygon and inheritage data?

Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents ...
-1
votes
1answer
461 views

SQL to get points within a certain distance

I am new to using spatial data types in sql server. I have managed to create a column of Approx 50,0000 geometry points (from x,y data) and would like to find out which (count number of) points are ...
1
vote
1answer
162 views

Multiple input shapefiles for ogr2ogr sqlite spatial operations

I'm running up against an issue doing a spatial operation requiring multiple input shapefiles using ogr2ogr and sqlite. I have: ogr2ogr -f "ESRI Shapefile" output.shp input.shp -dialect sqlite -sql ...
0
votes
0answers
40 views

Can I create a table showing all the Srid values for all the tables in my DB?

Hi I can identify the current SRID of one table (at a time) in my database, but I was wondering if there was a way to bring back a single table that would tell me all the datasets that do not ...
1
vote
0answers
97 views

Using sql SELECT DISTINCT in Fusion Tables API

How do I go about using sql SELECT DISTINCT in fusion tables API?
2
votes
2answers
158 views

Trying to use Python code and user defined inline parameters to build a SQL expression

I am trying to build a tool where the end user can run a model and make a selection from a feature class based on 3 different fields, calculate the total shape length, & export the results to a ...
1
vote
2answers
158 views

Select layer by attribute using SQL statement LEN() in ArcMap

as the title already suggests... I want to query the length of a string column and thought to use: LEN([ColumnName]) > 5 However, that doesn't work at all. I'm querying a table in a fGDB in ArcMap ...
0
votes
0answers
67 views

Select By Attribute sql problems [duplicate]

Having trouble with this python code. I keep getting a ERROR 000358: Invalid expression" code for the select layer by attribute management statement towards the end of the code. Moved the data to ...
1
vote
1answer
296 views

Subquery a list in select layer by attribute management using python

I am new to python and having trouble with my script. I want only the records from the COMTRS field (of the pur_dbf_view) that match values from the ComtrsList to be selected; however, I am getting ...
1
vote
1answer
125 views

Mapinfo SQL Query for compare for edited data in tables

I have a table that contain several columns, each has their own data. then, someone have made some change in the data (just a little one data editing). how do i know later what data have been ...
3
votes
1answer
160 views

Triggers for delta tables

I have been facing an issue to capture the delta updates on SDE. We have written triggers on A and D tables to capture the delta updates(to capture the version name based on state id's), however I ...
2
votes
1answer
69 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 ...
2
votes
1answer
66 views

How to select all tenth in a field? [duplicate]

I want to select all tenth in a field! :( I know this way; "Km" = 10 or "Km" = 20 ... and so, but I am pretty sure theres other way. I am not SQL literate, but with your help in this forums I have ...
1
vote
1answer
332 views

What is ArcGIS 10 definition query to select max value from table? [duplicate]

I want to select max date from may layer by below query in my Web GIS but I cannot. (SELECT MAX( GregorianDate ) FROM DrillingProgress WHERE Crew = 'c-Chabock')
3
votes
0answers
72 views

How to express not within/inside predicate in spatial database with good performance

In our application we want to express some spatial predicates as objects not within some areas, but unfortunately it has very bad performance. The following SQL is generated by our application: ...
0
votes
1answer
167 views

How can i save Shape file as sql script with ogr2ogr?

I have a some Shape files and i want to convert it into sql scripts. I know that ogr2ogr can put data into data base itself. But i want to have a sql script which can be executed and put data to DB. ...
1
vote
2answers
68 views

SQL: Select aggregated Bounding Box of multiple Features

I would like to calculate the aggregated bounding box of multiple features using OGC Simple Feature SQL or SQL API of ArcSDE. The following statement will only query the bounding box of each feature: ...
1
vote
1answer
101 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 ...
1
vote
0answers
49 views

How do I make vectorlayer with sql on PostGis via python code

I need to make a vectorLayer and fill with the SQL's results on PostGIS via python code. I'm working with python 2.7, QGIS 1.8 and PyQt 4.5 How do I it ? thanks
0
votes
1answer
152 views

ArcGIS - Help Using Oracle Function in Definition Query - No ArcSDE

I have set up a Query Layer pointing at an Oracle table with a SDO.Geometry column, and that is working and behaving fine. However, when I am attempting to set up a definition query that calls a ...
0
votes
1answer
576 views

How do I write a MYSQL spatial query to find all polygons (rectangle) that contain any points in a table?

Something like: SELECT * from polygons WHERE bounds CONTAINS(SELECT geom_point from points) Can someone help me out with the syntax? Bounds will always be a rectangular polygon and everything is in ...