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

learn more… | top users | synonyms

0
votes
1answer
21 views

whereClause syntax for Cost Distance parameter

I am looking to create a sql expression regarding point feature class and selection based on 'Name' field to pass to arcpy.sa.CostDistance() param[0]. similat to: savepath = env.workspace = ...
0
votes
0answers
86 views

How do I use Arcpy to label based on two different expressions, then convert to annotation?

I am trying to open an mxd and turn on labels before converting them to annotation. I would like to turn on labels with 2 different classes, based on 2 different expressions, then convert them to 1 ...
0
votes
1answer
69 views

How to create SQL statement to only return chainage values every 500m?

I have a list of chainage values every 100m (as a whole number) and I am trying to create an SQL statement to only return values every 500m. Any ideas? Using ArcGIS 10.1
2
votes
1answer
81 views

Conditional SQL Query

I've been running a code that searches a desired field in a feature class and performs an specified SQL query based on whether the field is string or double. The variables are just temporary fillers ...
1
vote
1answer
79 views

SQL within Python Script to get only part of the expression by GetParameterAsText

I was wondering if anybody could help me. Im trying to create a tool within ArcMap using a python script. Within the script i've been trying figure out how to change it so that I only need to get ...
1
vote
0answers
114 views

PyQGIS Dialog Plugin not resetting

I have written a PostGIS search plugin that works pretty well, except two things that I just cannot figure out a solution 1) When the dialog closes and then is reopened the previous query is still in ...
1
vote
0answers
37 views

Is it possible to retrieve lost versioned data in SQL Server Management Studio?

One of my staff accidentally deleted a feature class she was creating in her version before I reconciled it. I have grabbed a back up of the database from yesterday in SQL Management Studio. Is ...
0
votes
2answers
52 views

How can I optimize this point-linking query?

I have the following query, and am wondering how it could be optimised to run faster: WITH gps_nodes_1 AS ( SELECT "NODE_ID", "geog" FROM gps_nodes WHERE ST_Distance("geog",ST_GeogFromText('POINT( ...
0
votes
1answer
51 views

Set ArcGIS Tool Parameter to Obtain From it's own Source?

I am building a python tool in ArcGIS. When I build the parameters I will have 3: Choose a shapefile (parameter data type = shapefile) Choose an SQL query (parameter data type = SQL Expression) ...
1
vote
1answer
39 views

How to refer to a variable from the conditional statement in the ExtractByAttributes function in ArcGIS 10?

Trying to use the ExtractByAttributes for rasters so that I have multiple attribute values that I want to extract. This works fine: myraster = arcpy.sa.ExtractByAttributes("source_raster", "VALUE IN ...
2
votes
1answer
345 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 ...
0
votes
1answer
47 views

How can I combine two datasets with the same schema in to one table?

So I have two tables (in CartoDB/PostGIS) that are from two tiger shapefiles that have the exact same attributes, just in different counties. I want to combine them in to a single table, just ...
3
votes
1answer
617 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 ...
0
votes
1answer
231 views

How can I show numbers as percentages in CartoDB?

I would like to format numbers as percentages in cartodb. What are the best functions to display numbers with decimals, comma separators or percenteges? Thanks
0
votes
0answers
96 views

Group Nearest neighbor query in SQL Server 2012 spatial database

I am looking for way to perform GNN query over road networks in SQL Server. Any pointers
0
votes
1answer
34 views

Flag More Than One Space in SQL

I am working on an XP machine with FME'13 and I have a Address field and I have to find the spaces(middle spaces and not the leading and trailing ) in each and every row. St._Petters_Road is the ...
-1
votes
4answers
963 views

What is a SQL Query to all values in a column with Select by Attribute in ArcGIS? [closed]

I would like to select all values for an attribute using select layer by attribute to use in model builder and hopefully save the expression for automation purposes. If I have an attribute named ...
0
votes
1answer
260 views

How do I query XY Event Layer in Model Builder?

I am trying to build a model to input table data, make xy event layer and then select features by an SQL expression and make that a shapefile. What tool should I use for the SQL expression to select ...
1
vote
3answers
227 views

How do I use SQL in the field calculator to populate a new column?

Courtesy of Geofabrik I have a "places" point shapefile which includes in the attribute table a column named "type". As can be seen in the attached image the column contains fields such as "city", ...
1
vote
1answer
100 views

MSSQL server 2008 dissolve sql query

I have a set of spatial objects in a MSSQL server 2008 R2 trying to dissolve adjoining objects where one of the attributes are equal. The table below is somewhat representative to my original table: ...
0
votes
0answers
21 views

GeoDatabase user access based on administration boundry

I have silverlight application consuming Map service. Now I want to create user login. The scenarion is somethinkg like when a user logs he should only be able to query on data belongs to his area. ...
0
votes
2answers
62 views

sql query to get origins and destinations

I have a table of cities with an ID and geometry (200K rows) and a table of travels, with only two ID: idfrom, idto (7K rows) I'm wondering how could I select the geometries for each one in order to ...
2
votes
2answers
148 views

How can I create a hexbin layer using the SQL and CartoCSS parameters in cartodb.js?

CartoDB has a great hexbin visualization wizard baked into the client dashboard: However, I like using cartodb.js to customize events and interactions; in particular, I'd like to make styles ...
-1
votes
1answer
244 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
103 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 ...
1
vote
1answer
65 views

Rewrite query to match two points with a linestring in a different database table

OK, not sure if I've worded the title brilliantly, but here goes: Suppose I have: a table "gps_nodes" containing columns: "EASTING" (containing longitude data) "NORTHING" (containing latitude ...
3
votes
1answer
596 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 ...
5
votes
3answers
683 views

Sqlite, Python 2.7 and Spatialite

I am trying to spatially enable my sqlite database: import sqlite3 db = r'c:\temp\mydata.sqlite' conn = sqlite3.connect(db) conn.enable_load_extension(True) cur = conn.cursor() cur.execute(r"SELECT ...
1
vote
2answers
177 views

How to form SQL Expressions with mix of integer/numeric and text/string fields/values in ArcPy?

I am trying to create a query for the select by attribute function within a Python script.This is my current code. lot = '96' dp = 756421 lotFile = ...
5
votes
1answer
278 views

Is it possible that a good spatial index just cannot be made?

I have about 6 million rows with geography spatial features - they are all 1000 meter radius hexagons all over the US. There are a couple hundred thousand that would have been NULL, which I updated ...
1
vote
1answer
174 views

Calculating difference between bearing values in degrees

Suppose I have 3 geographic points, A, B and C. I can calculate the bearing of B from A (in degrees) using this SQL snippet: degrees(ST_Azimuth(geogA,geogB)) as brg_a_b ...and the same of C from ...
4
votes
2answers
655 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 ...
3
votes
1answer
69 views

Use selected value in postgis function

How can I use the selected value from a SELECT statement in a PostGIS function. e.g. using the geom_way from SELECT geom_way FROM osm_2po_4pgr ORDER BY ST_Distance(ST_GeomFromText('POINT(21.323329 ...
1
vote
1answer
39 views

How to do “random” distribution of certain classes to certain amount of polygons with respect to spatial constraints

I have a polygon layer. Now, I would like to assign/allocate/classify these Polygon into 13 different classes respecting spatial constraint, in my case: neighboring polygons (sharing line or nodes) ...
0
votes
0answers
76 views

Multiple geometry in a cursor SQL

I have a cursor which I create from a table which has one geometry field only. But in my cursor I would be using two geometry fields as I need to calculate two different areas.Below is the sql ...
1
vote
1answer
536 views

How do I insert a geometry as WKT to SQL Server Spatial with PHP?

I have a web mapping application built on Bootleaf (Leaflet + Bootstrap). Using the leaflet Draw plugin the user can create a polygon on the map. I'd like to insert that polygon into a geometry ...
2
votes
2answers
156 views

Can I use the LEFT (string) operator in ExecuteSQL()?

In QGIS I can use the LEFT operator on a dataset like this: LEFT( "Name2",3) ='031' What is the syntax for using this expression with ogr in python? Isn't this the right syntax? ...
0
votes
1answer
190 views

How do you create a CartoDB viz with a dynamic SQL statement?

I want to create a CartoDB viz where the where parameter in my SQL statement changes dynamically at runtime. When I share my CartoDB viz from the dashboard, I can't help but notice the the SQL ...
2
votes
0answers
37 views

Multiple Cursors

Below is the sql statement which I have explained below. Declare Cursor c1 is select store_number ,0 as total_area,0 as BG_ID ,0 as store_geom from table1 where CLIENT_ID= 1 and ORG_ID ...
4
votes
2answers
1k 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 ...
0
votes
0answers
33 views

SQL cursor issue

I am trying to create a new table using a cursor and a select statements. Declare Cursor c1 is select store_number from table1 where CLIENT_ID= 1 and ORG_ID = 1 ; Type C1_TAB_TYPE ...
2
votes
1answer
853 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
99 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
67 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
56 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
73 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
312 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
461 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
52 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 ...
1
vote
2answers
153 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 ...