2

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 query an Oracle db using an Oracle tool. So is it like SQL is a language and each software uses its own slang of the same language ?

EDIT: Thanks for those answers! Now do you guys know of a documentation somewhere that would teach me how to make a query in QGIS field calculator. I can make a simple one using the implemented functions, but what about something more complex (using case when, where, and so on) ?

5
  • 1
    Yes. SQL is notorious for having different 'dialects'. Spatial SQL is a little more homogeneous though and there is a little more standardization between software (though not completely so). Commented Oct 8, 2013 at 10:20
  • Which part of QGIS are you referring to?
    – Nathan W
    Commented Oct 8, 2013 at 10:45
  • I'm referring to the field caculator
    – Legzav
    Commented Oct 8, 2013 at 11:50
  • When I look at the w3schools.com SQL tutorials, it also looks pretty different than what I'm supposed to do in QGIS or ArcView...
    – Legzav
    Commented Oct 8, 2013 at 11:55
  • SQL tutorials is for Oracle SQL 'dialect'
    – Mapperz
    Commented Oct 8, 2013 at 13:30

3 Answers 3

5

In GIS applications, often times the SELECT * FROM portion of the SQL expression is typically supplied for you, effectively leaving the user to just enter the WHERE clause.

Additionally, applications often abstract the SQL language to make it easier for their customers to use & understand (who may not know the ANSI SQL standards), choosing to implement their own implementation of the SQL language for consistency. In some cases, like ArcGIS, the syntax changes based upon the underlying RDBMS.

To query file-based data, including file geodatabases, coverages, shapefiles, INFO tables, dBASE tables, and CAD and VPF data, you use the ArcGIS SQL dialect that supports a subset of SQL capabilities. To query personal geodatabases, you use the Microsoft Access syntax. To query an ArcSDE geodatabase, you use the SQL syntax of the underlying DBMS (that is, Oracle, SQL Server, DB2, Informix, or PostgreSQL).

For example:

  • When querying shapefiles, you enclose fields in double quotes: "AREA"
  • When querying a personal geodatabase, you enclose fields in square brackets: [AREA]
  • When querying a file geodatabase, you enclose fields in square brackets: "AREA"
  • When querying ArcSDE: it depends on the source.

NOTE: SQL operators, reserved words, and functions may also be database dependent when using this methodology for "passing through" the SQL dialect. Querying DATE fields can be particularly challenging because of formatting expectations.

3

So is it like SQL is a language and each software uses its own slang of the same language ?

It is exactly like that.

3
  • Can you provide an example of this? that would be a good answer.
    – Mapperz
    Commented Oct 8, 2013 at 13:31
  • Oracle allowing the (+) for outer joins. The difference in the names for OGC calls in MS Sql Server vs PostGIS. Index hints in SQL Server. Etc... Commented Oct 8, 2013 at 16:23
  • Postgis : SELECT ST_AsText(geom) FROM ... MS SQL : SELECT geom.STAsText() FROM ... In Postgis : SELECT .. FROM "Areas" is case sensitive where same in MS SQL is not. Getting whole ROW to string is one function in Postgresql in MS SQL its something like 10 lines of code which you need to code yourself. etc etc Commented Jan 24, 2014 at 8:47
3

There are actually SQL standards, see also this Wikipedia article, but this standard is only implemented by major object-relational databases like PostgreSQL or MySQL.

What QGIS uses in its field calculator is kind of a custom query language with a syntax that resembles SQL but is far away from implementing any standard. The QGIS documentation says that explicitly:

QGIS has some support for parsing of SQL-like expressions. Only a small subset of SQL syntax is supported.

1
  • And the one to Wiki, this one will probably help me pretty much.
    – Legzav
    Commented Oct 9, 2013 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.