Is it possible to work with spatial object attribute tables in Delphi and dbExpress?

Question

I have a spatial database in Mysql where the geometry and attributes of space objects are stored.

I tried to create a basic form, as basic as possible using Sqlconnection, Provider, Dataset, Clientdataset and dbware, to edit object information.

The difference was when I gave the select * in the dataset, because Delphi doesn’t have data support like Geometry mysql, then deletes the type field Geometry.

Apparently it works normally but attribute edits are not done even giving the apply updates, is it necessary to do something else in Delphi? or dbExpress does not perform that process?

Has anyone ever tried to work on that approach I mentioned?

If someone wants to play is SQL with a minimal BD with only one space object.

-- --------------------------------------------------------
-- Host:                         localhost
-- Server version:               5.6.20-log - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             9.1.0.4867
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for gis
CREATE DATABASE IF NOT EXISTS `gis` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `gis`;


-- Dumping structure for table gis.amf
CREATE TABLE IF NOT EXISTS `amf` (
  `OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
  `SHAPE` geometry NOT NULL,
  `id` decimal(10,0) DEFAULT NULL,
  `name` varchar(254) DEFAULT NULL,
  `hectares` double(19,11) DEFAULT NULL,
  `x` double(19,11) DEFAULT NULL,
  `y` double(19,11) DEFAULT NULL,
  `x2` varchar(254) DEFAULT NULL,
  `y2` varchar(254) DEFAULT NULL,
  UNIQUE KEY `OGR_FID` (`OGR_FID`),
  SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- Dumping data for table gis.amf: 15 rows
/*!40000 ALTER TABLE `amf` DISABLE KEYS */;
INSERT INTO `amf` (`OGR_FID`, `SHAPE`, `id`, `name`, `hectares`, `x`, `y`, `x2`, `y2`) VALUES
    (1, _binary 0x0100000001030000000100000028000000981F7D4A75C12441B8CA9D5EF0966141B032C3DF00C12441B8C1F28EED96614138FF43758AC02441D3D16617EA96614178F362ED1DC024418ED5CE32E7966141A87C8B0BA2BF2441B00B4DDDE3966141D07EE85D49BF244117C4C4C5E1966141280649E105BF24416CFB560DE0966141F8590FB0B5BE24415A8EB6FEDD966141803BCD6574BE24415827993CDC966141E0E057200ABE2441089569FED9966141C0D6349EC5BD24419259D6E9D89661418806CBA263BD24413A7ACDEDD6966141F0B4B7B208BD2441CB8DC6AED496614150D59F29C4BC2441D037F4FCD2966141D08DA97C56BC244166144B8CD1966141E01DDBBDC1BB24417640D08ECF96614118A4DFD17BBB24413E383C93CE966141A052A72116BB2441EC2C9925CD966141E8E54440B2BA244119A0437ECB966141B845560660BA2441D6B6ADC6CA9661419898D87F51BA2441400EEBB0CA9661412053A4974BBA2441F76170A9CA966141E04A35303BBA244188D5D88ACA9661414895531721BA244178653593CA966141C040138131BA24411C059293D09661414072122648BA24412F8540BFD696614158E3F2B25FBA244192584EE1DD966141D8D01AAF7CBA2441746B3DEBE5966141F085D2E48DBA2441B958AB38EA966141B0319E98ADBA2441E0D27747F3966141A0C0DAAA05BB24410856ABC3F3966141504C6CA2CCBB244141DF224BF496614130FFEE88A4BC24414FB6CD9DF5966141600BB362E8BD2441AC9C224FF2966141D0CC553C2EBE244180172257EE966141B03E682B3DBF2441EAD4215BEC96614120000B0583BF2441175A2253F0966141F85D729A0CC02441EDA7551EF1966141B82C875AABC02441653C0075F0966141981F7D4A75C12441B8CA9D5EF0966141, 0, '15', 16.49848504330, 679523.38008600000, 9221914.59363000000, '37° 22\' 28.99" W', '7° 2\' 11.06" S'),
    (2, _binary 0x010000000103000000010000000F000000607EBC0915C324415FF1C17222986141C0F183A4F0C32441AB0917A120986141509A4CBA44C424412E6821AC2098614118BC4BD85BC42441FD722AAF2098614118BC4BD85BC42441ADD5A28AEF9761417078C58A72C12441ADD5A28AEF976141D85EABFB8DC12441B06C4447F4976141583CE3B788C12441278960ADF497614118DEF9B838C124411242E3BCFA97614178E70D295DC124414FDF792B0298614198B630A475C12441D65DBE2907986141F0FE3734ADC124417689BF1710986141F83C3ED4DCC12441EC4116AD1A98614120D84DE453C2244142436CCD1F986141607EBC0915C324415FF1C17222986141, 0, '1', 14.02872517270, 680315.53151200000, 9224249.43888000000, '37° 22\' 3.45" W', '7° 0\' 54.97" S'),
    (3, _binary 0x010000000103000000010000000E000000E0F1BC9EE5C824419B563413CC97614120C4DC7CF9C424419B563413CC97614120C4DC7CF9C42441ADD5A28AEF9761411076A680C5C82441ADD5A28AEF976141482A7C2FCCC8244159396DECE9976141E0057BD8D4C82441BC577D19E1976141A0C0B4D1D6C8244153932FA4DD97614100AA5039D8C8244121840B2EDB97614120A96BE0D8C824410968B41FDA976141F0807455DCC82441E611F2D5D5976141E010739DDCC824410596A57CD5976141A8375421E1C82441B8355B4CD19761413816A60AE3C82441041F1FFCCD976141E0F1BC9EE5C824419B563413CC976141, 0, '4', 14.02769543310, 680819.96229800000, 9223917.75257000000, '37° 21\' 46.97" W', '7° 1\' 5.71" S'),
    (4, _binary 0x010000000103000000010000001800000050F9D70F3AC5244194DF57CC20986141F8872DB289C524418AF03CB319986141689151C889C52441772350B319986141288D41C79CC5244174C8DC0118986141E0D0D67AF1C5244182919BE610986141F86F7FCB1FC62441517B0CA00C986141E060961756C624418AAE313F07986141F037CC0AEEC62441B8B78EDF05986141E80F424422C72441C92F020F06986141507089AE89C7244156127FF20598614158B836A107C824417BF7EF9005986141281E566059C8244135E2B66005986141C8845F0EADC824413C9724B305986141D0D045C0B1C824414FDF792B0298614130588ACDB4C82441D85C11E0FF976141B8617710B9C824412A14D60FFB97614150297804BFC82441E358157BF5976141E89EDBE3BFC82441278960ADF49761415893DF18C5C82441273AE0E1EF9761411076A680C5C82441ADD5A28AEF97614120C4DC7CF9C42441ADD5A28AEF97614118BC4BD85BC42441ADD5A28AEF97614118BC4BD85BC42441FD722AAF2098614150F9D70F3AC5244194DF57CC20986141, 0, '2', 14.02784704860, 680721.06814300000, 9224201.91733000000, '37° 21\' 50.23" W', '7° 0\' 56.47" S'),
    (5, _binary 0x010000000103000000010000000D000000588E94CEE1C624418253F36FA9976141B0444721C8C424418253F36FA9976141B0444721C8C424419B563413CC97614120C4DC7CF9C424419B563413CC976141E0F1BC9EE5C824419B563413CC976141185D7DB4E6C824415A7B7A45CB97614140E27DACEAC82441A2A3A462C7976141B877860AF6C82441F0D65A68BA97614130B784A1F8C82441390A7073B7976141307C8084FEC82441DA00F7BAB097614150D86B76FEC8244149CCC5BAB0976141683E9E8556C72441FF934BF1AA976141588E94CEE1C624418253F36FA9976141, 0, '6', 14.02825620420, 680805.13664500000, 9223644.06518000000, '37° 21\' 47.43" W', '7° 1\' 14.62" S'),
    (6, _binary 0x010000000103000000010000000D00000020C4DC7CF9C42441ADD5A28AEF97614120C4DC7CF9C424419B563413CC976141B0444721C8C424419B563413CC976141A891D01764C124419B563413CC976141D871379C18C12441E611F2D5D5976141B84A00A208C12441224EDAE6D7976141B81AC36EBCC02441224EDAE6D797614118D1EE2CCCC0244153932FA4DD976141A866927BCFC024412E37DBD8DE976141D8D4D54E4BC124411B84DCC4E89761417078C58A72C12441ADD5A28AEF97614118BC4BD85BC42441ADD5A28AEF97614120C4DC7CF9C42441ADD5A28AEF976141, 0, '3', 14.02846133560, 680324.71767800000, 9223917.64989000000, '37° 22\' 3.11" W', '7° 1\' 5.77" S'),
    (7, _binary 0x010000000103000000010000001600000010CA1A3C25C124418253F36FA997614110CA1A3C25C12441DDFA37C37B976141E05DB08364BD2441DDFA37C37B9761413049B26360BD24413A2A026281976141409F090F3CBD2441AB91FA0990976141A8B24E8136BD24417885844792976141F0B854F232BD24417885844792976141C823A1432EBD2441A1B1152B9497614110E3304C65BD24419CD5C1699F976141B0F2B503CDBD24416B746DB0A697614180C22F8865BF24418ED14ADAA2976141C874C66345BF24417885844792976141706EC0F248BF24417885844792976141F06D473947BF24413621E2639197614188D527744CC02441AB91FA099097614100531A247AC024418B857BCD8F976141986AEB4C7AC02441AB91FA0990976141A8B5E2CF7BC024417885844792976141F0BBE84078C024417885844792976141885B336685C0244147445CC3A5976141E8F4718A80C024418253F36FA997614110CA1A3C25C124418253F36FA9976141, 0, '7', 13.92974024410, 679826.91611100000, 9223287.42356000000, '37° 22\' 19.26" W', '7° 1\' 26.34" S'),
    (8, _binary 0x010000000103000000010000000C000000B0444721C8C424419B563413CC976141B0444721C8C424418253F36FA997614110CA1A3C25C124418253F36FA9976141E8F4718A80C024418253F36FA9976141B0EA853B70C02441453B3CC5B59761416034A9F070C02441390A7073B7976141E8E85C2F72C02441F0D65A68BA9761410022536873C02441B19EA34FBD97614130C0D36E3BC124413DCBD61CBD976141708F3FB564C124419EBED8FECB976141A891D01764C124419B563413CC976141B0444721C8C424419B563413CC976141, 0, '5', 14.02830437450, 680292.02617900000, 9223630.66760000000, '37° 22\' 4.14" W', '7° 1\' 15.11" S'),
    (9, _binary 0x010000000103000000010000000B000000E82F60DFA3C124416914F1C255976141207B949292BE24416914F1C2559761417098F9057CBE24417CC5B9E561976141E0FB11D320BE2441C75A66E26897614148A92CCAF8BD2441B7F498F36B9761419825BE7BB2BD24417A00726D6F976141980109F96ABD2441E9CE88F672976141E05DB08364BD2441DDFA37C37B97614110CA1A3C25C12441DDFA37C37B976141E82F60DFA3C12441DDFA37C37B976141E82F60DFA3C124416914F1C255976141, 0, '9', 14.02819636160, 679911.46004000000, 9222992.94250000000, '37° 22\' 16.47" W', '7° 1\' 35.92" S'),
    (10, _binary 0x010000000103000000010000001B000000E82BE81E3EC32441D5C4B38D9C9761417889F68975C3244178858447929761413083F01879C324417885844792976141A0D96B897CC324411A4348A491976141E8B5E8CB86C32441AB91FA0990976141B83AC71487C32441DE6798FE8F97614138295ED4D1C32441CD40ABB08A976141F0C684FF10C42441D649003784976141101D6DCC47C42441CE8380977E9761411093BC6362C42441DDFA37C37B976141E82F60DFA3C12441DDFA37C37B97614110CA1A3C25C12441DDFA37C37B97614110CA1A3C25C124418253F36FA9976141B0444721C8C424418253F36FA9976141588E94CEE1C624418253F36FA9976141A0DB8D81D9C62441E75D8B54A9976141B88D2E708DC624417038CB36A8976141504A7DD129C624410F8D29C0A6976141600452C5BEC5244121F24C3AA597614168875CF15EC5244125BA440BA4976141E84FB84A08C52441C7D4AF15A3976141F8E0C2A3D4C424413412AF43A2976141A87CFE180CC424413C62BD7F9F9761410063C691C2C3244105124E7B9E976141F0EA7DC77FC324415E8A36939D976141007EBF1B3EC3244148B1498E9C976141E82BE81E3EC32441D5C4B38D9C976141, 0, '8', 13.92952781570, 680289.53027100000, 9223329.55106000000, '37° 22\' 4.19" W', '7° 1\' 24.92" S'),
    (11, _binary 0x010000000103000000010000000E000000E030C396E0C224416914F1C255976141E030C396E0C22441B9E82C952D97614130DA2A7EB5BF2441B9E82C952D976141D8C2628A95BF24412DCDF8F139976141F03E6FCAF4BF2441A634D70B3E97614170054A98E0BF24410C1C17EA4197614158C7882CB3BF2441B02F2E9D4A97614198DCE1924DBF24413FF072514E976141D8A56BECD4BE24418CFF1CB0499761418030981FA2BE24411BC061644D97614150E8FDB19FBE2441BCC8ECB24E976141207B949292BE24416914F1C255976141E82F60DFA3C124416914F1C255976141E030C396E0C224416914F1C255976141, 0, '11', 14.02833711550, 680081.38860400000, 9222678.34417000000, '37° 22\' 10.90" W', '7° 1\' 46.14" S'),
    (12, _binary 0x010000000103000000010000001000000048FE0724ECC524416914F1C255976141E030C396E0C224416914F1C255976141E82F60DFA3C124416914F1C255976141E82F60DFA3C12441DDFA37C37B9761411093BC6362C42441DDFA37C37B976141C88038D47FC424413B515AA178976141880B914D98C42441E258AF50769761415863F14EECC4244195478441739761415821A0650BC524414236593270976141F097F6E711C524417A00726D6F976141988B6A4749C52441C75A66E26897614170806B9360C52441EA79A9216697614110C44D2074C524413B73030D649761418863C2B392C52441292A5F716097614188879861CDC5244182361AD85997614148FE0724ECC524416914F1C255976141, 0, '10', 14.02804577130, 680380.12466700000, 9222971.45419000000, '37° 22\' 1.20" W', '7° 1\' 36.56" S'),
    (13, _binary 0x010000000103000000010000000B00000090F06BADAEC42441B9E82C952D97614190F06BADAEC424416D1B30FBF39661418015C9CE8CC424414D1E147DF396614168B93ECEF2C32441E20542CF18976141B0B0841AEEBF244141AD74A414976141A0971546E2BF24411AC9B0081A97614188C99BFDC1BF244171E14BBF289761412031CA1ABBBF2441FBE056692B97614130DA2A7EB5BF2441B9E82C952D976141E030C396E0C22441B9E82C952D97614190F06BADAEC42441B9E82C952D976141, 0, '13', 12.89274416940, 680250.52935000000, 9222394.36923000000, '37° 22\' 5.35" W', '7° 1\' 55.36" S'),
    (14, _binary 0x01000000010300000001000000170000007054F1A1BDC42441C91BDF32F496614190F06BADAEC424416D1B30FBF396614190F06BADAEC42441B9E82C952D976141E8D0A4D7A5C62441B9E82C952D976141980F70BAAFC62441FBE056692B97614148531B2BBFC62441BDD73705289761418025004FD6C62441F26FDE202297614130F9F173EAC6244180A973811C976141485A40D0EFC6244167098DE31A97614180C19504F3C624411AC9B0081A976141703D72A504C72441A14CAF541597614158031D7815C7244101A366B50F97614110E30C141BC72441475513700D976141186696304CC72441BDAC7431FC966141083D41834DC7244157E6C95DFB966141E0B7A2744DC724412D51A35DFB966141800228078DC62441C2A3C961F9966141C053234F69C624418D40F4F7F896614180F9863854C62441756CD0C0F896614190D96EDCCBC52441A12F3574F7966141B8DD85F789C5244170F436CDF6966141285488AE0AC52441450FC151F59661417054F1A1BDC42441C91BDF32F4966141, 0, '14', 12.89252647880, 680682.27854300000, 9222283.26931000000, '37° 21\' 51.27" W', '7° 1\' 58.93" S'),
    (15, _binary 0x010000000103000000010000000F000000E8D0A4D7A5C62441B9E82C952D97614190F06BADAEC42441B9E82C952D976141E030C396E0C22441B9E82C952D976141E030C396E0C224416914F1C25597614148FE0724ECC524416914F1C255976141F852637DFBC52441A09D67B95397614190850EFF11C62441325A446A4F976141E876033C14C62441BCC8ECB24E9761412071F56D2CC62441CF2DE8F44697614158FB2D3944C624410C1C17EA4197614170C68DB250C62441ED615E453F976141A05DF7CE6FC6244114EBD676399761414059488F87C62441235F61F233976141D804E4B899C624417060A13E30976141E8D0A4D7A5C62441B9E82C952D976141, 0, '12', 14.02823761420, 680523.28082700000, 9222663.87336000000, '37° 21\' 56.50" W', '7° 1\' 46.56" S');
/*!40000 ALTER TABLE `amf` ENABLE KEYS */;


-- Dumping structure for table gis.geometry_columns
CREATE TABLE IF NOT EXISTS `geometry_columns` (
  `F_TABLE_CATALOG` varchar(256) DEFAULT NULL,
  `F_TABLE_SCHEMA` varchar(256) DEFAULT NULL,
  `F_TABLE_NAME` varchar(256) NOT NULL,
  `F_GEOMETRY_COLUMN` varchar(256) NOT NULL,
  `COORD_DIMENSION` int(11) DEFAULT NULL,
  `SRID` int(11) DEFAULT NULL,
  `TYPE` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table gis.geometry_columns: ~0 rows (approximately)
/*!40000 ALTER TABLE `geometry_columns` DISABLE KEYS */;
INSERT INTO `geometry_columns` (`F_TABLE_CATALOG`, `F_TABLE_SCHEMA`, `F_TABLE_NAME`, `F_GEOMETRY_COLUMN`, `COORD_DIMENSION`, `SRID`, `TYPE`) VALUES
    (NULL, NULL, 'amf', 'SHAPE', 2, 1, 'POLYGON');
/*!40000 ALTER TABLE `geometry_columns` ENABLE KEYS */;


-- Dumping structure for table gis.spatial_ref_sys
CREATE TABLE IF NOT EXISTS `spatial_ref_sys` (
  `SRID` int(11) NOT NULL,
  `AUTH_NAME` varchar(256) DEFAULT NULL,
  `AUTH_SRID` int(11) DEFAULT NULL,
  `SRTEXT` varchar(2048) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table gis.spatial_ref_sys: ~0 rows (approximately)
/*!40000 ALTER TABLE `spatial_ref_sys` DISABLE KEYS */;
INSERT INTO `spatial_ref_sys` (`SRID`, `AUTH_NAME`, `AUTH_SRID`, `SRTEXT`) VALUES
    (1, NULL, NULL, 'PROJCS["SIRGAS_2000_UTM_Zone_24S",GEOGCS["GCS_SIRGAS_2000",DATUM["Sistema_de_Referencia_Geocentrico_para_las_AmericaS_2000",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",500000.0],PARAMETER["False_Northing",10000000.0],PARAMETER["Central_Meridian",-39.0],PARAMETER["Scale_Factor",0.9996],PARAMETER["Latitude_Of_Origin",0.0],UNIT["Meter",1.0]]');
/*!40000 ALTER TABLE `spatial_ref_sys` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Answer 1

Updating: The solution found to circumvent this problem, according to the author was to make a SELECT in all fields except the field of type Geometry, and change the update mode of upWhereAll for upWhereKeyOnly.

When placing modified fields on query and the ID the update will be based on specific key columns. So the code below is not required.

---

As quoted by you, the problem happens when this data is imported into your program, which instead comes in format xx.xxxx returns xx,xxx and because of this ApplyUpdates doesn’t work.

You can try to make a replace exchanging , for . thus:

function replaceComma(const str : string ): string;
begin
Result := StringReplace(str, ',', '.', [rfReplaceAll]);
end;

That one replace can be called before saving the data, you can use it in the event BeforePost of DataSet.