[Geoserver-jp] SQL View Feature

[Mario Basa]

Hello,

I don't know if anybody is using it already, but I am having so much
fun testing the new SQL View feature of Geoserver 2.1-beta1.

What I did to test the new feature is to get the map data
japan_ver52.zip over at ESRI (gold sponsor of FOSS4G 2010 :-) here:

http://www.esrij.com/products/gis_data/japanshp/japanshp.html

and import it to PostGIS.

Then I got statistics data from the National Census (平成17年 ) here:

http://www.e-stat.go.jp/SG1/estat/GL08020103.do?_toGL08020103_&tclassID=000001007609&cycleCode=0&requestSender=search

and import some of the data from the from the Excel file into a
PostgreSQL table. I used the 市区町村コード(renamed to jcode) as the link
item with the japan_ver52 PostGIS table.

>From here, I created a PostGIS Data Store in Geoserver, and then
created the SQL View Layer using this sql string:

select population,
  pop_male,
  pop_female,
  pop_15under,
  pop_15to64,
  pop_65over,
  pct_15under,
  pct_15to64,
  pct_over65,
  household,
  pct_single,
  pct_nuclearhsld,
  the_geom
from census2005 a,japan_ver52 b where a.jcode = b.jcode

And that is it.

In older versions of Geoserver, this was a 2 process procedure: (1)
create a view in the DB, which usally strict DBAs will not permit, (2)
create a PostGIS layer in GeoServer using the view. This isn't very
efficient since if the view is changed in the DB, the layer info also
has to be modified in GeoServer.

Anyway, it is possible to view the sample census  layer with the
thematic engine I did over here:

http://mbasa.homelinux.net/geothematics/?layer=mbasa:census2005

It is a slow machine though. Next I'll try using the dynamic parameters:

   where pref = %pref%    or    where prefcode = %prefcode%

and pass the parameters from OpenLayers. Hopefully it can handle Kanji
characters, although I doubt it.


Regards,

Mario.