Hey, we took a Shape file and using shp2mysql we converted it to a .sql dump. This dump was then inserted into a mysql database which GeoServer recognized after we configured it. The server works fine and renders the map based off the mysql database. The problem though is the rendering takes well over a minute. We haven't done exact timings but it can range from 1-5 minutes to render the map. The Shape file took less than a minute usually to render. The shape file was a world shape file containing a global map of the world.
Has anyone rendered a map via MySQL more precisely a world map? Below is our table structure.
`the_geom` geometry NOT NULL,
`ID` int(11) NOT NULL default '0',
`OBJECTID` int(11) NOT NULL default '0',
`ISO3_CODE` varchar(255) NOT NULL default 'NA',
`STATUS` varchar(255) NOT NULL default 'NA',
`CAPITAL` varchar(255) NOT NULL default 'NA',
`Terr_ID` int(11) NOT NULL default '0',
`Terr_Name` varchar(255) NOT NULL default 'NA',
`Color_Code` varchar(255) NOT NULL default 'NA',
`Shape_Leng` double(19,11) NOT NULL default '0.00000000000',
`Shape_Area` double(19,11) NOT NULL default '0.00000000000',
PRIMARY KEY (`ID`),
SPATIAL KEY `the_geom` (`the_geom`(32))
Ali Khan ha scritto:
Hey, we took a Shape file and using shp2mysql we converted it to a .sql dump. This dump was then inserted into a mysql database which GeoServer recognized after we configured it. The server works fine and renders the map based off the mysql database. The problem though is the rendering takes well over a minute. We haven't done exact timings but it can range from 1-5 minutes to render the map. The Shape file took less than a minute usually to render. The shape file was a world shape file containing a global map of the world.
Hmm... very slow in fact, but then again the mysql datastore is not efficiently coded in fact. Can you give me the shapefile somehow so
that I can try out? Maybe by compressing it and putting it on some ftp
server?
To have such a slow rendering I expect the shapefile to be something
well over 50-100 MB, right?
Cheers
Andrea
I have to check with the legal department about giving over the shapefile as it isn't a public file. If we can though I will happily put it out for you to look at. The shape file itself is about 30MB. the Mysql export done by shp2mysql.exe creates a 70mb .sql file to export to mysql. The Multipolgon on average though has alot of points. Some examples: US has 112114 points in a multipolygon, china 64316, brazil 28860, spain 7134, peru 724.
My original guess was there was too many points to be drawn which was what is slowing it down, but i do not have other maps to compare to. Are the above amounts of points normal for the geom?
Andrea Aime wrote:
Ali Khan ha scritto:
Hey, we took a Shape file and using shp2mysql we converted it to a .sql dump. This dump was then inserted into a mysql database which GeoServer recognized after we configured it. The server works fine and renders the map based off the mysql database. The problem though is the rendering takes well over a minute. We haven't done exact timings but it can range from 1-5 minutes to render the map. The Shape file took less than a minute usually to render. The shape file was a world shape file containing a global map of the world.
Hmm... very slow in fact, but then again the mysql datastore is not efficiently coded in fact. Can you give me the shapefile somehow so
that I can try out? Maybe by compressing it and putting it on some ftp
server?
To have such a slow rendering I expect the shapefile to be something
well over 50-100 MB, right?
Cheers
Andrea
Ali Khan ha scritto:
I have to check with the legal department about giving over the shapefile as it isn't a public file. If we can though I will happily put it out for you to look at. The shape file itself is about 30MB. the Mysql export done by shp2mysql.exe creates a 70mb .sql file to export to mysql. The Multipolgon on average though has alot of points. Some examples: US has 112114 points in a multipolygon, china 64316, brazil 28860, spain 7134, peru 724.
My original guess was there was too many points to be drawn which was what is slowing it down, but i do not have other maps to compare to. Are the above amounts of points normal for the geom?
No, they are in fact too many, and you can't expect a server to be able
and serve quickly an general map made with those data. A way to quicken
it up is to perform generalization on the data and play with the style
controls to choose which layer (or geometry column, if you choose to
replicate them in the same table) is the more appropriate for the
current resolution.
Yet one minute for the shapefile seem like too much to me anyways,
should be more around 30 seconds...
I look forward to have a look at the data and see if there is anything
obviously wrong.
Cheers
Andrea
Andrea, thanks for the help you were right there was simply too many points. We found a map that was less detailed. The borders are a bit less accurate but good enough for our purposes. The database size is much smaller now and the map loads within seconds. Thanks for the help.
For reference we used the world map located at http://www.vdstech.com/map_data.htm
Direct link is at http://www.vdstech.com/mapdata/world.zip
Andrea Aime wrote:
Ali Khan ha scritto:
I have to check with the legal department about giving over the shapefile as it isn't a public file. If we can though I will happily put it out for you to look at. The shape file itself is about 30MB. the Mysql export done by shp2mysql.exe creates a 70mb .sql file to export to mysql. The Multipolgon on average though has alot of points. Some examples: US has 112114 points in a multipolygon, china 64316, brazil 28860, spain 7134, peru 724.
My original guess was there was too many points to be drawn which was what is slowing it down, but i do not have other maps to compare to. Are the above amounts of points normal for the geom?
No, they are in fact too many, and you can't expect a server to be able
and serve quickly an general map made with those data. A way to quicken
it up is to perform generalization on the data and play with the style
controls to choose which layer (or geometry column, if you choose to
replicate them in the same table) is the more appropriate for the
current resolution.
Yet one minute for the shapefile seem like too much to me anyways,
should be more around 30 seconds...
I look forward to have a look at the data and see if there is anything
obviously wrong.
Cheers
Andrea
Ali Khan ha scritto:
Andrea, thanks for the help you were right there was simply too many points. We found a map that was less detailed. The borders are a bit less accurate but good enough for our purposes. The database size is much smaller now and the map loads within seconds. Thanks for the help.
Nice.
For reference we used the world map located at http://www.vdstech.com/map_data.htm
Direct link is at http://www.vdstech.com/mapdata/world.zip
Ah, yeah, I already had this small one. I was more interested in the
big beast :-p
Cheers
Andrea