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

learn more… | top users | synonyms

4
votes
2answers
135 views

How to build QgsExpressions in python?

How can I build a feature request expression using variable inside expression? For example values = [1, 2, 3] for j in range( y ): it = vl.getFeatures( QgsFeatureRequest().setFilterExpression ( ...
4
votes
1answer
1k views

Cartodb - SQL query of public visualization of a private table

The team at CartoDB have recently(last few days) made some useful updates that now allow public visualisation maps to reference data in private tables. The change has only been made in the last day ...
4
votes
1answer
896 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 ...
4
votes
1answer
1k views

Select by attribute expression invalid in python

I'm not sure how my expression is failing. Any ideas? inPoints = "points.shp" i = 0 arcpy.MakeFeatureLayer_management(inPoints, "pts") arcpy.SelectLayerByAttribute_management("pts", "NEW_SELECTION", ...
4
votes
2answers
867 views

Plotting large numbers of objects on a chart using R and WKT format

I am retrieving thousands of geometry lines in WKT format from a Microsoft SQL Server database. I would like to plot all of those lines in one map, but I am having some difficulty with the code. How ...
4
votes
2answers
1k views

Using a bounding polygon, instead of a bounding box

I've got a large set of LAT/LON coordinates. Up until now, when I needed to select out those that fell within a certain region, I'd just create a bounding box using some MINx, MAXx, MINy, MAXy values ...
4
votes
4answers
6k views

Select by attributes - records with the largest attribute value

Using 'Select By Attributes' how can I select records from a shapefile database table where a numeric field contains the largest value. Need to use MAX function or something like that but can't get ...
4
votes
3answers
1k views

Automatically creating 2D-point features for ArcGIS Desktop from addresses in SQL database?

I currently have an SQL-Table which contains our buildings. I already added the table and now want to create features ( 2D-Points ) from that information. The biggest problem I have is that the ...
4
votes
2answers
1k views

Execute SQL in File Geodatabase

Is there a way to execute SQL queries on file geodatabase from Python? I'm looking for the same functionality provided by arcpy.ArcSDESQLExecute for SDE databases. Thanks,
4
votes
1answer
585 views

How to populate SQL options for parameter in ArcGIS 10 tool?

I'm trying to create a tool where individuals can select a variety of records using a SQL expression that is then passed to a calculation. I can add a parameter under the tool, and select SQL ...
4
votes
3answers
698 views

How to select a number of rows based on current sort order

I would like to be able to select a certain number of rows from my attribute table based on their ordering and not their attributes. For example, if I sort by ObjectID, I then want to select the top ...
4
votes
1answer
1k 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 ...
4
votes
1answer
3k views

Performing Geoprocessing SQL Queries in QGIS from a PostGIS Database

This may be a dumb question, but is there way to run a PostGIS geoprocessing SQL query in QGIS? Specifically, I am thinking of running SQLs such as ST_Union, ST_Split etc etc. I have tried the ...
4
votes
1answer
376 views

ogrinfo SQL GROUP BY Clause

Does ogrinfo support a GROUP BY clauses? I could not find any good documentation of all the SQL syntax features it supports. I tried the following: ogrinfo . -geom=no -sql 'SELECT lsad FROM ...
4
votes
2answers
663 views

Merge reversed linestring in correct order in postGIS

I want to merge linestring in the correct order in postGIS, but sometime they are reversed so something like this : select astext(linemerge(ST_union( ST_GeomFromText('LINESTRING(3 3, 2 2)'), ...
4
votes
1answer
1k views

is there any direct access to Mapbox data? (similar to CartoDB's sql api)

I've been asked to add a form to a map that will write data in the background (e.g., ajax to a magic file that does datawrites and some emailing, notifications etc) and a new marker will be added to ...
4
votes
2answers
692 views

how to cast integer to float in an ogrinfo sql statement?

I have a shapefile for which I would like to add the values in a column. As the sum exceed the capacity of an integer, I need to cast values to a float. I tried the following: ogrinfo -sql "SELECT ...
4
votes
3answers
1k views

Incorporating SQL Statements in ModelBuilder?

I have a point data set where each point has a unique operator associated with it. I'd like to select by attribute = operator name and generate a new shapefile for each operator containing only the ...
4
votes
3answers
2k 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 ...
4
votes
1answer
583 views

Geodjango insert statement: psycopg2.IntegrityError: violates check constraint

Why is the following INSERT statement throwing this error? INSERT INTO domes_place (placeid, structidx, oscode) VALUES ('10', '1', 'POINT(51.121282945772 1.3138398501222)'); psycopg2.IntegrityError: ...
4
votes
1answer
291 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 ...
4
votes
3answers
2k 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. ...
4
votes
3answers
364 views

Filtering dataset using SQL or Python in ArcGIS Desktop?

Using ArcGIS Desktop 10.0 SP4. I have a large dataset of sites that needs to be filtered down to a usable size. There is an attribute for the site_ID and another one for the site_type. There are ...
4
votes
1answer
325 views

How to update attribute values of points outside an area?

I have two datasets in PostGIS 1.5 (on Postgres 9.1) one of polygons and one of points. I am hoping to create a SQL command that will select all of the points in a dataset that are not within of the ...
4
votes
1answer
589 views

Shorter Version of this SpatiaLite Query

I've been doing some spatial queries using spatialite, and they're large enough that a spatial index becomes a must. An example of using an index is given in the tutorial spatialite> SELECT ...
4
votes
1answer
248 views

PostGIS Query: All rows within 1 hour of each other which intersect

I'm using postgres (postgis) and i want to perform a query that returns all rows which geometries intersect and have a timestamp within 1 hour from each other. What would the SQL statement look like? ...
4
votes
1answer
387 views

Sort by geometry using INHERITS?

hope everyone's doing well. I have a statewide point data set (parent table: 600K rows, 500 columns) I need to analyze at multiple scales based on several different spatial divisions. I'll also need ...
4
votes
1answer
702 views

Error using ST_AddPoint for adding a new point to a polyline in PostGIS

I have two PostGIS tables, one where I store points, named "nodes" and another with where I store multilinestrings, named "lines". I'm trying to append a new vertex to a line using one of the nodes ...
4
votes
1answer
52 views

How to use MAX SQL statement in ArcGIS?

I would like to use a MAX SQL query for a column in a definition query, where there records that are being queried are the MAXIUM from the field UPDATE_DATE_DATE while grouping by the field ...
4
votes
1answer
248 views

Showing Linestring from SQL statement with CartoDB

I have cartodb sql statement to return what polygons are intersected by a linestring, but I'd like to have the linestring visualized over the polygons on the results. Also not add the linestring to ...
4
votes
1answer
2k views

Good place to get Country, State, City, and Zipcode information?

I'd like to populate a database with common location information. I'm not entirely certain that addresses across the globe have similar or even the same entities as the Country, State, City, and ...
4
votes
1answer
36 views

Cartodb API Read Only

Is it possible to generate an API-key in Cartodb with read-only permission? Seems like the default API-key has full controll
4
votes
1answer
372 views

SQL-query in arcpy - SELECT WHERE feature between two values

I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used". whereclause = ...
4
votes
1answer
597 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 ...
4
votes
3answers
196 views

Sql syntax in Model builder

In ModelBuilder I am trying to perform the following query, using the select by attribute tool. I am having great difficulty with the sql syntax. What I am trying to do is Select Top 3 (Avg) Price, ...
4
votes
1answer
469 views

Is it possible to run an arcpy execute SQL method without connecting to any database or server?

I want to simply process a layer by writing a tool script in a toolbox without having to connect any database, so I think I may have to set up a workspace environment in ArcMap, but I'm not sure if I ...
4
votes
1answer
2k views

Python script for constructing a WHERE clause from user input

I'm trying to construct a where clause from user input that will eventually be passed through as a SQL statement in the select tool or the select by attributes tool. I've started a script thats been ...
4
votes
1answer
67 views

Merging multiple feature classes in file geodatabase with ogr2ogr?

Is there a way to merge multiple feature classes into one feature class in a geodatabase with ogr2ogr? If this isn't possible with ogr2ogr, is there another method? I'm using the ESRI File Geodatabase ...
4
votes
0answers
57 views

Create polygon from lines in PostGIS

I'm trying to divide a polygon in smaller polygons from a line, but I think I can't use the st_split function. What I need is to create small polygons inside a big one, using linestring grid. I've ...
3
votes
3answers
10k views

Defining a Query with Multiple Values

I have been trying by best to create a definition query which would select a group of municipalities from among the many in my state. I have tried commands of all sorts of variations from ...
3
votes
3answers
3k views

Inserting polygon centroids into an additional column in PostGIS

I'm trying to calculate the x and y centroids for a table using ST_Centroid. The documentation says that it returns the geometric center of a geometry. I first made a column X: ALTER TABLE ...
3
votes
2answers
278 views

Column 'SHAPE' cannot be null in mysql database even if the query string is correct

I am trying to write an POLYGON into an column named SHAPE which is a geometry type. And created query which looks $sql = "INSERT INTO rokka(zone,district,location,area,SHAPE) VALUES ...
3
votes
1answer
746 views

What does “foo” stand for in this PostGIS command?

The help page for the PostGIS function ST_PixelAsCentroids has this line of code as a usage example: SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM dummy_rast ...
3
votes
1answer
2k 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 ...
3
votes
1answer
453 views

SQL Server data modelling + spatial data = what book? [closed]

What recommended book can you provide about data modelling for geographical spatial data (geometry) in SQL server?
3
votes
2answers
991 views

ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement

I have a ListBox listing the unique values of a column in a feature class; the user can select any number of values and click a button to then zoom to the set of features matching those values. A ...
3
votes
1answer
88 views

PostGIS - improving sql statement

I've managed to create the below statement that creates a table based on a point in polygon query. However it creates a column that gives a true/false value which I then delete out all the f values ...
3
votes
1answer
1k 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 ...
3
votes
1answer
694 views

Why is Spatialite showing dates in MM-DD-YYYY format?

I have a table with a dates column that is formatted in MM-DD-YYYY format, as shown in SQL SELECT statements. I'm using Qspatilite (QGIS 1.8) and Spatialite GUI. When I ORDER BY the date column it ...
3
votes
3answers
2k views

How to Calculate length of polyline geometry for several tables in PostGIS?

PostGIS database contains several geometry (polyline) tables (with names "D1_r", "D2_r", "D3_r"). I calculate length for one "postgis layer" use request: SELECT ...