EasyLoader & PostGreSQL/PostGIS

[Alex Eastwood]

Hi all,

when uploading MapInfo tables to a PostgreSQL/PostGIS database any logical columns are converted to Char(1) and the data is lost. You can prevent data loss by manually converting the logical column to Char(1) before uploading, but it means I'm going to have to check for a DBMS setup for every Select statement querying a logical column since our clients will not all be moving over to a DBMS backend.

I know that PostgreSQL and surely the vast majority if not all other databases have a Boolean datatype so why does EasyLoader not convert Logical fields to this datatype?!

I'm using the EasyLoader that ships with the 32bit v15 MapInfo Pro. The only workaround I can think of would be to write a function in the database to retrospectively add a Boolean column back to the database table and update it based on the Char(1) column.

Any suggestions?

Cheers,

Alex

 

[Alex Eastwood]

Update:

my workaround of changing the Char(1) column back to Boolean in the database won't work after all since when you then open the table back in MapInfo it changes the Bool column back to Char(1) with True and False represented as 1 and 0, respectively, which is the same underlying value for any Logical columns in MapInfo so why are these Logical and Boolean data types incompatible?!

Alex

[Cliff B]

Hi Alex,

Mapinfo doesn't have a true boolean type for the tables it's just a char(1) field with T or F stored in it, the same goes for map basic it isn't treated as 0 or 1, easyloader assumes its a char field and therefore had no implied casting from char to boolean.
I've tried this with OGR2OGR hoping its third party mapinfo tab driver might have handled it but it suffers the same problem.
You're probably stuck with making an interim query table replacing T with 1 and F with 0

[Alex Eastwood]

Hi Cliff,

Ah, I was going to try with OGR2OGR but looks like I won't need to!

Interesting though what you say about MapInfo not having true boolean data type, is this not what the logical columns are? True and False are defined in the MapBasic.def file as 1 and 0, I've had a look around and it seems that in most programming languages and database infrastructures the underlying value of True and False are always integers.

But anyway, looks like we'll have to do it the long way! Thanks for your help and if you do know any more about the above I'd be interested to hear it!

Cheers,

Alex

[Uffe Kousgaard]

Cliff is wrong. MapInfo has a boolean field and variable type.
Actual storage format is not relevant.

The problem lies in various converters.

Regards
Uffe Kousgaard

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alex Eastwood]

...can the problem be fixed? If MapInfo and the DBMS both support Logical/Boolean datatypes then the fact that the 2 aren't compatible should be seen as a bug.

Alex

[Cliff B]

Whilst it has a Logical data type its not a true boolean in the single bit 1 and 0 sense.
which is the problem here as anything reading the table sees it as a char field not a single bit binary value.

I agree with Alex though it is not great that mapinfo's ODBC interface doesn't handle it transparently.

--
Cliff

[Cliff B]

Yeah OGR2OGR has gotten me out of many a pickle, its my go to for GIS data translation these days.

Most databases and programming languages do treat it as a 1 or 0 but as single bit. When testing an integer they simply look for a non zero value. 
Its been a while since I've done this and I'm at home so don't have mapbasic to double check, but I recall mapbasic being far more fussy and also being able to compare TRUE to T and getting a positive value, but I might be getting mixed up.

[Cliff B]

Just one final thing to add to this.
I've done some playing in the mapbasic windows in MI15 actually looked at the DAT file in a hex editor, and mi 16(64bit) is storing it as a 1 and a 0.

I guess it must fall back to the fact it's a modified version of the DBF database format, where Logical fields are stored as a Byte, with a default value of " " and possible values of T and F
http://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm

I've really got no solution beyond saving it as an int and letting the database driver handle the data translation. (i've tested this with OGR2OGR and it works, I don't have the postgres ODBC driver installed so couldn't test it with Easyloader though)

You might even get away with this in any mapbasic scripts you run as an integer should still match the 0 and 1 which as you pointed out are the defined values for TRUE and FALSE

[Alex Eastwood]

Hi Cliff,

thanks for all your help and suggestions. Other stuff has come up with work that needs to be dealt with, but I'll get back to this soon I'm sure and if I find anything else that's relevant or useful I'll update this thread.

Cheers,

Alex