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

learn more… | top users | synonyms

4
votes
2answers
185 views

ArcGIS Model isn't accepting my SQL Query

I'm running a model that breaks a dataset into several dbf tables based on a query. However, only half of the query is being honored. The PopDenZone field is not being accepted and I am getting other ...
4
votes
3answers
800 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], array[1],...
4
votes
1answer
489 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?
4
votes
2answers
229 views

Correct SQL expression when looping different fields

I'm having trouble with the follow lines of code. Practically what I am doing is a 10 shapefile loop in which field represents the name of the field that I want to be calculated( the name differs by ...
4
votes
2answers
149 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
812 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
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
1answer
103 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
200 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 ( u'"...
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

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
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
2answers
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
999 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
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
92 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
658 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
798 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
407 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
714 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
776 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
2k 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
612 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
332 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 2013-...
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
396 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
342 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
3answers
2k views

Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

I need to build a sql server db trigger to update a date field with the current date when a record is inserted or updated, I think. I will be using the date field as a way to recognize updates/...
4
votes
1answer
600 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
399 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
728 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
313 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
1k 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, fields,...
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
2answers
3k views

Adding rows of data to a non-spatial table in ArcMap with a SDE SQL Server for Metadata purposes

I'm attempting to use several non-spatial tables to better organize the large set of feature classes I continually add to a SDE Geodatabase (SQL Server Express). So I have several tables setup and ...
4
votes
1answer
239 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
1answer
43 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
470 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
668 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
2answers
210 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
514 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 ...