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

learn more… | top users | synonyms

4
votes
2answers
129 views

In terms of SQL, what exactly is Query Layer's “Unique Identifier” doing?

When you make a link with a database using Query Layer in ArcMap, you must select a Unique Identifier, which ensures that the resulting Layer only has unique records in the Attribute table. In some ...
4
votes
2answers
1k 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
770 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 ...
4
votes
2answers
1k views

Selecting points not snapped

I have a point layer and a polyline layer. I want to select all points in the point layer that are not snapped to a vertex in the polyline layer. How can I accomplish this? I'm using MapInfo and ...
4
votes
1answer
84 views

Using Not Equal to Wildcard in ArcMap Definition Query?

I'm attempting to use a definition query in order to display only a subset of data. I have a large CAD drawing that I have imported into my map. I do not need many of the lines from the .dwg file to ...
4
votes
2answers
168 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
1k 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
938 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
7k 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
1answer
70 views

Selection by attribute string in ArcGIS 10 with wildcard as part of the name

I have an attribute field in my file geodatabase (GDB) where I'm trying to select all records that contain an underbar in the string field. The issue is that the underbar is considered a SQL wildcard ...
4
votes
2answers
2k 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
630 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
765 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
389 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
688 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
754 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
608 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
324 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
3k 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
381 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
336 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
597 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
253 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
398 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
718 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
64 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
286 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
41 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
447 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
651 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
204 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
502 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
161 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
84 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
12k 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
2answers
335 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
809 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
475 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?