[postgis-tickets] [PostGIS] #3278: MySQL FDW Issues with Geometry Type

[PostGIS]

#3278: MySQL FDW Issues with Geometry Type
------------------------------------------+---------------------------
 Reporter:  ryckingnoaa                   |      Owner:  pramsey
     Type:  defect                        |     Status:  new
 Priority:  medium                        |  Milestone:  PostGIS 2.1.9
Component:  postgis                       |    Version:  2.1.x
 Keywords:  MySQL FDW, geometry, polygon  |
------------------------------------------+---------------------------
 The following bug has been logged on the website:

 Bug reference:      13616
 Logged by:          Ryan King
 Email address:      ryan(dot)king(at)noaa(dot)gov
 PostgreSQL version: 9.4.4
 Operating system:   Red Hat 4.4.7-11), 64-bit
 Description:

 Not all the records return when selecting geom columns:
 select * from public.public_zones --only 54 records return
 select polygon_column from public.public_zones --only 54 records return
 select OGR_FID from public.public_zones --all 3000 records return
 select name from public.public_zones --all 3000 records return

 These are the steps I took to create the FDW:

 MySQL:

                 CREATE TABLE `public_zones` (
                   `OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
                   `polygon_column` geometry NOT NULL,
                   `name` varchar(254) DEFAULT NULL,
                   UNIQUE KEY `OGR_FID` (`OGR_FID`),
                   SPATIAL KEY `polygon_column` (`polygon_column`)
                 ) ENGINE=MyISAM AUTO_INCREMENT=18519 DEFAULT
 CHARSET=latin1;


                 CREATE VIEW `public_zones_view` AS
                 select `public_zones`.`OGR_FID` AS
 `OGR_FID`,st_astext(`public_zones`.`polygon_column`) AS `polygon_column`,
                 `public_zones`.`name` AS `name` from `public_zones`;



 PostgreSQL:

                  CREATE EXTENSION mysql_fdw;

                  CREATE FOREIGN DATA WRAPPER mysql_fdw_db1 HANDLER
 mysql_fdw_handler
                  VALIDATOR mysql_fdw_validator;

                  CREATE SERVER mysql_svr_db1
                  FOREIGN DATA WRAPPER mysql_fdw_db1
                  OPTIONS (host 'IPADDRESS', port '3306');

                  CREATE USER MAPPING FOR username SERVER mysql_svr_db1
                  OPTIONS (username 'username', password 'password');

                  CREATE FOREIGN TABLE table_name (
                    OGR_FID SERIAL NOT NULL,
                    polygon_column public.geometry NOT NULL,
                    name varchar(254) DEFAULT NULL,
                  SERVER mysql_svr_db1
                  OPTIONS (dbname 'mysqldbname', table_name
 'table_name_view');


 I read a post that there was a bug in PostGIS 2.1.0 preventing foreign
 tables from being output in geometry_columns and geography_columns views
 and
 was supposed to be fixed in PostGIS 2.1.1. We are on 2.1.8.

--
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3278>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.