This project is read-only.

sf.table.query: general rules and specific characters

Mar 10, 2014 at 10:59 PM
Hello, I've been developing an application with mapwingis. I've managed to make the basic table.query commands work (as in selecting by a specific field with AND and OR operands).

1) is it possible to use more sql commands like let's say "LIKE"? If so what would be the query I would have to make?

2) what version of sql is table.query using (I would like some documentation if there are advanced features)

3) I seem to have a problem with characters like these "ąčęė" in fields of the table. Both my program and mapwindow gis crash if I try to search with those letter and don't return results when searching in those fields. Is there anything that can be done about it?
Mar 26, 2014 at 11:56 PM
Dec 9, 2014 at 11:44 AM
Reviewing it before v. 4.9.3 release: our built-in expression parser is not SQL based and for the time being doesn't support functions. Therefore complex expressions should be calculated as new table fields. I updated docs for Table class to reflect the matter (online version will be updated when beta is released) . The issue with non ASCII characters still needs testing.
Marked as answer by pmeems on 12/9/2014 at 4:24 AM
Dec 9, 2014 at 12:40 PM
I post this with trepidation, as it is clearly a cheap hack, but one which seems to work well for me. I use the MS visual foxpro oledb driver to issue complex queries to attribute table(s), returning sfmwids or other unique field(s). The resulting list bypasses having to create a compound field in the table, and can be used for purposes of shape selection, etc. While effective for sfs of moderate size or smaller, its performance would likely be disappointing for very large ones. In that case you could presumably build an .ndx--although I haven't needed to and therefore not tried. One big drawback would be that the index would have to be rebuilt whenever a shape is added or removed.

Like I said, a hack, use at your own risk.

Don

PS A quick word of thanks to the development team. I've found the software to be both extremely powerful and rock solid. Kudos and thank you! Which reminds me I'm probably overdue for my next donation...
Dec 10, 2014 at 9:12 AM
Thanks Don for this 'hack' it might be useful for some.

Nice to mention donations. With the next release (due in 2 weeks) we will also request donations for the development of MapWindow v5.
We will show a nag screen with the following page: http://www.mapwindow.org/documentation/mapwingis4.9/MapWindow49.html when you start MapWindowv4.9 (lite version of MapWindow v4.8.8, but with the new features of MapWinGIS v4.9.3) which comes with the next release.

Thanks,

Paul
Dec 10, 2014 at 12:57 PM
An update: I tried to reproduce the issue with non-ASCII characters. However I haven't got any crashes. If the characters belong to the locale other than currently selected in the system they will be replaced with question marks. Can be reproduced with code like this:
table.StartEditingTable();
if (!table.EditCellValue(0, 0, "正体字"))    MessageBox.Show("Failed to set new value");
if (!table.StopEditingTable())    MessageBox.Show("Failed to stop editing");
Debug.Print("The value is set: " + table.get_CellValue(0, 0).ToString());
I checked QGis and they have the same behavior. But ArcGIS it seems supports individual encoding for DBF files with UTF-8 as a default one:
http://support.esri.com/cn/knowledgebase/techarticles/detail/21106 Quite a nice feature to add to our wish list.

@Don, thanks for foxpro oledb driver suggestion. We could have tried to handle our expressions with some DBF driver. However it would hardly be viable for in-memory shapefiles (temporary write data to disk?), plus it's good to have our parser not-dependent on any external drivers which may or may not be present on the target machine. So if it comes to it, I'd rather extend the existing parser with additional functions.
Dec 10, 2014 at 5:01 PM
Spotted this comment in shapefil.h by accident, adding it here as a reminder:
  • Revision 1.41 2007/12/15 20:25:32 bram
  • dbfopen.c now reads the Code Page information from the DBF file, and exports
  • this information as a string through the DBFGetCodePage function. This is
  • either the number from the LDID header field ("LDID/<number>") or as the
  • content of an accompanying .CPG file. When creating a DBF file, the code can
  • be set using DBFCreateEx.