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

learn more… | top users | synonyms

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? ...
2
votes
1answer
601 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
107 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 ...
2
votes
2answers
675 views

Merge lines with the same 'name' attribute on QGIS?

I have a shapefile with a few thousand lines, each is a street segment. I'd like to merge all features with the same 'streetName' attribute into a single attribute. I only really care about the name ...
2
votes
1answer
673 views

How to get polygons from PostGIS table to PHP array best?

Currently I fetch polygons from a database table using ST_AsText(). The results are in text format, like the following. POLYGON((84 104,212 48,321 122,243 179,275 238,131 240,84 104)) I am not ...
2
votes
2answers
132 views

Trying to use an integer in a WHERE clause

I'm trying to use an integer value in a WHERE clause (looking at an integer field). expression = "'SERVICE' = 'Service' " expression = expression + 'AND ' expression = expression + "'GPS_TIME_TOTAL' ...
2
votes
1answer
215 views

SQL Command to convert pgRouting result to GeoJSON

My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to ...
2
votes
1answer
250 views

How to return nearest point to input lat/long with distance?

I am trying to write a query with PostGIS that returns one row where the geometry of that row is the closest to an input point. I would also like to include the distance between these points in the ...
2
votes
1answer
402 views

How to create a point table from a remote csv file?

The current approach that I am using to load a csv file into a PostGIS enabled database on a server is the following: -- create table CREATE TABLE points( id int, lat double precision, lng double ...
2
votes
1answer
948 views

How to delete records from multiple, similar feature classes by attribute values using an SQL statement?

I have several hundred geodatabase feature classes which all have the exact same fields. I need to go through each of those feature classes and delete any records which match an SQL query (the same ...
2
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 ...
2
votes
1answer
872 views

How to extract data from a POINT column in PostGIS using JAVA (jdbc)

How do you get a value from a column which has a point data type. I've just switched over from MySQL to PostGIS. I have a column which is called point and is of type Point. I want to get that value ...
2
votes
1answer
672 views

Convert SqlGeometry data from SQL directly to Tiles / PNG file

I would like to know a basic method for 'exporting' SqlGeometry data from SQL Server 2008 to a Tile Server / PNG File generator. I already have a Silverlight Map application which uses a Custom Tile ...
2
votes
2answers
52 views

Query for numeric field against text field that contains a numeric value

In my database, I have one field, Service_Day, formatted as numeric, which contains a single digit number 1 through 7 (for days of week). I want to compare it against another field, called Region, ...
2
votes
1answer
64 views

If do not want to use pgRouting then what?

Good evening fellow GISers, I am interested in creating an online routing application. So supposing I have all the data I want into a postGIS database and loaded onto GeoServer and I am willing to ...
2
votes
2answers
45 views

SQL 2012 code that spatially polyline fine (join file) to a point file (target) and provides Distance Field

I am currently working in ArcMap 10.2.2 but would like the SQL code to create the same output as that provided by the Spatial Join tool. Target feature = point, Join Feature = polyline, Option ...
2
votes
1answer
229 views

How to 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 ...
2
votes
2answers
368 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 ...
2
votes
1answer
115 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
338 views

How to merge connected lines with same direction (PostGis)

I am looking for a way to join all lines that are connected, have the same direction, i.e. start point of one is end point of the other, and share some common attributes. In fact, I want to connect ...
2
votes
2answers
160 views

Problem with SPIT (error while importing layers to POSTGIS)

I was attempting to import a polyline roads layer into postgis, using both SPIT in QGIS 1.7 and postGIS GUI for this purpose. I have received errors through both processes as described here: SPIT ...
2
votes
1answer
359 views

How do I select * rows of multiple columns in one query using Select by Attributes within ArcMap?

I am trying to convert a psql query string into arcpy formatting for an UpdateCursor where expression parameter: '''SELECT ...
2
votes
1answer
156 views

How to create a network with all the track points

I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database. Now I have a table which holds all the track points of these ...
2
votes
1answer
134 views

Join Zip Codes Table to Nearest City from Second Table

I have two tables, siteareas and ziplookup. The former contains large cities in the US; the latter all US zips with lat/lngs. I have a foreign key into the former table in the latter: sitearea_id. I ...
2
votes
1answer
252 views

Creating a model to run a complex query

I'm just a rookie when it comes to creating models in ArcGIS. I have two shapefiles from which I need to calculate an AVG and a MAX erosion rate for individual properties. The two shapefiles are: ...
2
votes
2answers
1k views

Convert RT90 into WGS84

Goal: Gain WGS84 decimal to locate some cities in Google Map. Problem: I don't know how to convert the value of the RT90 coordinate system into WGS84 in SQL Server.
2
votes
0answers
51 views

SQL Query does not work in ModelBuilder (but works elsewhere)

I am having issues getting a simple SQL query to work in model builder. It works fine when I use it with the standard "Select features by attributes tool", but ModelBuilder doesn't find the required ...
2
votes
1answer
33 views

SQL Server equivelent to Arcmap Intersect for polygons?

I have three different sets of GEOMETRY Polygon datasets in SQL Server that completely overlap. I need to create a new dataset that has a polygon for each unique area of the three layers. So ...
2
votes
0answers
36 views

SQL Error Message: Find and Replace for Date Field

Hi, I am attempting to use the find and replace function within the Attribute Table Options to replace fields that contain dates in the following format: month/day/year (e.g. 03/14/2014). The ...
2
votes
1answer
80 views

Model Builder Query by date

I've created a model that does several spatial joins based off of a lat/long in a table. In the table, I have a "Modified_date" which gives year, month, date, time. This model is set to run ...
2
votes
2answers
125 views

Definition Query / SQL problem in ArcGIS

I have been working on some Definition Queries for layers in ArcGIS (10.2.0.3348). The layers connect to data in a File Geodatabase. Now I have attempted to implement the following query (1), that ...
2
votes
1answer
63 views

How to find a nearest line to a point

I have a table with many points that need to be associated with street segments to calculate an average value. should I do a relationship for all points to a street segment that is closest. I have ...
2
votes
2answers
163 views

Selection subquery using another table in database ArcGIS

I need to 'extract' a list of variables based on the IDs from another table. Using a subquery seems to be the best method. I have a large list of variables and have outside calculated a second list. ...
2
votes
1answer
77 views

Error at or near “select” PostGIS

I'm using node.js to connect to a PostGIS database to check if an input feature significantly intersects with an existing feature. The error seems to be with the nested SELECT statement - can anyone ...
2
votes
2answers
138 views

Finding Polygons which do not touch any other polygon in a single oracle database table

I currently have an oracle table of approximately 100,000 records - all of which are polygons. I want to find all the polygons which do not touch any other polygon within the same table. There are a ...
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 ...
2
votes
0answers
36 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 ...
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. ...
2
votes
0answers
55 views

What is the MS SQL Spatial syntax for counting points within polygons? [duplicate]

All, What I'm trying to do is this: Count points within polygons There are thousands of points and maybe 2 dozen polygons Get a list back of the 2 dozen polygons by name and the count of points ...
2
votes
1answer
309 views

Raster as Attribute Field Error

I'm using Arc 10.1. I am creating a parcel inventory where I would like to have a photo of the property attached as an attribute and view-able with an HTML popup. I have a polygon feature class built ...
2
votes
1answer
339 views

SQL Server UNion of geography feature returns FULLGLOBE result?

I have some trouble with sql server 2012. I store coordinates of french cities in my database as geography object (with ESRI 4326) and i want to make the UNION of some cities. I have in result ...
2
votes
0answers
127 views

Matching points in table to polygons

this is my first post to this forum. I hope to start participating actively. I have a table with several records (aprox 200.000), each with its own coordinates. And I need to identify the "Polygon" ...
2
votes
0answers
171 views

Select a distinct attribute with maximum shape area

I am trying to manipulate a large dataset containing a lot of sliver polygons resulting from an overlay operations. (The eliminate tool is very time consuming to generalize them.) I identified the ...
1
vote
3answers
5k 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 ...
1
vote
1answer
171 views

Select By Attribute: Between Two Numbers [closed]

I am trying to select house numbers that are between 200 and 499. I've been using: "HOUSE_" >= '200' AND "HOUSE_" <= '499' ... and it's coming up with almost everything else. The numbers it ...
1
vote
3answers
272 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 ...
1
vote
1answer
164 views

What are FLOOR and CEILIING in PostGIS designated for?

Just a short question to lighten up my understanding of some PostGIS-code: Regarding the creation of user-defined grids I often find the expressions CEILING and FLOOR. Like in this non-representative ...
1
vote
4answers
724 views

How to find the center point of MULTILINESTRING?

How to find the center point of MULTILINESTRING, which lies on the multiline?
1
vote
1answer
199 views

Why does this SQL syntax produce empty feature class with ArcGIS Select_analysis?

I'm working on a script that involves an arcpy.select_analysis function. The problem I have is that when I run the script it produces an empty file. I've verified that the whole thing works when in ...
1
vote
5answers
94 views

Selectively removing whitespace from a string with SQL

I have two columns of adresses that I need to join. Column 1 has the adresses formatted as "Some Street 12 A" while column 2 has them formatted as "Some Street 12A." Ie. the last whitespace between ...