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) ?

  • 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). – MappaGnosis Oct 8 '13 at 10:20
  • Which part of QGIS are you referring to? – Nathan W Oct 8 '13 at 10:45
  • I'm referring to the field caculator – Legzav Oct 8 '13 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 Oct 8 '13 at 11:55
  • SQL tutorials is for Oracle SQL 'dialect' – Mapperz Oct 8 '13 at 13:30
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.

  • Can you provide an example of this? that would be a good answer. – Mapperz Oct 8 '13 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... – Russell at ISC Oct 8 '13 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 – simplexio Jan 24 '14 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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