How to get this postgis data into a format I can use with Google Maps/KML_问答_开发者

If I run the following query in postgis

select postcode, st_astext(geom) from testshp where postcode = 'L9 2DA';

I get the following:

"L9 2DA";"SRID=4277;MULTIPOLYGON(((336540.345408958 396573.341317112,336494.15657667 396544.939447063,336493.158924276 396544.659527189,336478.565166127 396540.970583139,336483.001009 396535.002291544,336481.467427635 396533.932597741,336483.347301566 396530.473587872,336468.283574934 396524.495299139,336468.217614446 396524.465307724,336464.499091889 396523.035716941,336455.998433894 396533.122829535,336454.720449423 396534.692380255,336452.626203903 396538.121398709,336450.408282467 396541.75035993,336449.188013424 396542.470153891,336448.371752375 396542.940019393,336447.621451815 396543.159956437,336446.3104871 396543.589833386,336445.090218057 396543.659813354,336443.498921264 396543.749787599,336441.907624472 396542.660099519,336440.934707262 396542.030279804,336440.283347435 396542.470153891,336420.792022992 396555.366462361,336420.40450512 396555.626387958,336420.437485365 396555.806336448,336422.094742646 396566.003417564,336422.218418562 396566.903160016,336421.501098247 396567.932865266,336420.684837198 396569.092533314,336419.909801454 396570.252201362,336419.777880477 396570.432149853,336419.184236077 396571.251915197,336418.466915761 396572.251629032,336417.658899774 396573.371308527,336416.751943052 396574.470993746,336415.877966576 396575.560681826,336414.064053134 396577.560109496,336413.247792085 396578.529831915,336412.250139691 396579.689499964,336410.345530577 396581.938856092,336409.405593611 396583.158506971,336405.934422888 396587.717202058,336402.529212653 396592.405859944,336401.564540504 396593.745476482,336400.533907867 396595.09509016,336399.65993139 396596.214769655,336398.752974669 396597.374437703,336397.878998192 396598.504114337,336395.496175534 396601.003398924,336394.152230574 396602.432989708,336393.22053867 396603.472692096,336392.47023811 396604.592371591,336391.810633222 396605.562094011,336391.093312906 396606.501825016,336389.939004352 396608.031387183,336387.498466266 396610.690625984,336386.797636072 396611.430414222,336386.311177467 396611.940268277,336385.437200991 396612.840010729,336384.596204758 396613.779741734,336383.532591876 396614.939409782,336382.625635155 396616.309017736,336379.781089075 396620.687764333,336382.279342589 396626.786018726,336388.966087142 396643.191322756,336409.595230017 396642.471528795,336406.066343865 396647.220169511,336405.060446411 396648.529794634,336404.095774262 396649.839419758,336403.345473702 396650.839133593,336402.191165148 396652.12876444,336401.498580015 396652.908541231,336400.937915861 396653.628335192,336399.684666573 396655.21788019,336399.066286991 396656.527505314,336395.685811939 396663.535499296,336397.376049465 396664.215304704,336400.715299211 396665.564918381,336408.597577624 396660.246440779,336409.685925689 396659.496655403,336410.502186738 396658.376975908,336411.433878642 396657.027362231,336412.340835363 396655.717737107,336413.190076657 396654.558069059,336414.121768561 396653.528363809,336415.003990099 396652.558641389,336416.941579458 396650.339276676,336417.906251607 396649.059642967,336418.780228083 396647.939963472,336419.65420456 396646.840278253,336420.470465609 396645.840564419,336421.220766169 396644.840850584,336422.12772289 396643.621199705,336423.001699367 396642.401548827,336423.315011689 396641.971671878,336423.875675844 396641.191895086,336424.782632565 396640.032227038,336425.590648553 396639.002521788,336427.404561995 396636.81314849,336428.311518716 396635.593497611,336429.408111843 396634.183901104,336430.471724725 396632.784301735,336431.378681446 396631.654625101,336433.159614644 396629.345286143,336434.03359112 396628.185618094,336434.874587353 396627.035947184,336436.779196467 396624.526665459,336437.842809349 396623.24703175,336438.873441987 396622.027380872,336439.904074624 396620.877709961,336440.84401159 396619.808016158,336442.435308382 396617.808588488,336443.218589187 396616.908846037,336444.158526153 396615.809160818,336445.090218057 396614.659489908,336446.3104871 396613.159919156,336447.497775898 396611.56037702,336449.592021418 396608.691198314,336450.408282467 396607.561521681,336451.40593486 396606.191913727,336452.716899575 396604.402425962,336453.623856297 396603.122792254,336454.563793262 396601.87314996,336455.437769739 396600.653499082,336456.34472646 396599.463839618,336457.09502702 396598.504114337,336458.686323813 396596.534678082,336459.527320045 396595.464984278,336462.091534047 396592.285894284,336463.064451257 396591.026254851,336464.688728294 396589.126798565,336466.189329415 396587.217345141,336467.121021319 396586.097665645,336468.184634201 396584.808034798,336469.248247083 396583.558392505,336473.222366534 396577.41015242,336489.003413481 396590.996263436,336469.248247083 396612.999964942,336443.935909503 396641.221886501,336504.809195612 396667.254434762,336520.903554882 396635.533514781,336521.216867203 396634.93368648,336524.127373772 396630.215037179,336525.281682326 396628.375563723,336540.345408958 396573.341317112)))"

If I run

select postcode, st_askml(geom) from testshp where postcode = 'L9 2DA';

I get

"L9 2DA";"<MultiGeometry><Polygon><outerBoundaryIs><LinearRing><coordinates>1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,开发者_C百科1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF 1.#INF,1.#INF</coordinates></LinearRing></outerBoundaryIs></Polygon></MultiGeometry>"

I'm totally lost as to how I get the data into a format I can display on google maps, i'm very new to postgis so if you can point me in the right direction I will be eternally grateful! ;)

Thanks in advance

leddy aka going crazy lady :D

You don't have to do this yourself if you make handy use of GeoServer's capabilities.

You can define a layer with a parameter. The parameter will be the zipcode in this case. In return you'll either get a completely rendered map image, a KML file, text, json, or whatever you like (you can even get it as a zipfile with shapefiles or a PDF file).

I'll describe step by step what to do. Once you have this set up, add new views is a piece of cake. Also downloading in other formats or overlaying stuff will be easy. But you need to invest a little time first.

Ok, here we go:

Download and install GeoServer
Go to the admin website
Go to the layers page
Choose Add new resource
Click Configure new SQL view....
Enter a name for the query. Enter PostCodeLayer for example.
Enter the following SQL code:

SQL Statement:

select postcode, geom from testshp where postcode = '%POSTCODE%'

Click Guess parameters from SQL. It'll detect the POSTCODE parameter in the SQL.
Click Save

From now on, you can see your stuff by adding this to your WMS url's: &viewparams=POSTCODE:L9 2DA

The rest will be done for you. GeoServer can serve up a complete KML that you can use in Google Earth or Google Maps, if you just provide your Postcode parameter.

To read more about GeoServer, go here: http://geoserver.org

To read more about SQL views in GeoServer, go here: http://docs.geoserver.org/latest/en/user/data/sqlview.html

Update

GeoServer generates KML output that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<kml>
   <Folder>
      <NetworkLink>
         <name>EasyMap:easy_finland_sections</name>
         <open>1</open>
         <visibility>1</visibility>
         <Url>
            <href><![CDATA[http://10.1.1.112:8080/geoserver/wms?height=1024&width=1024&layers=EasyMap%3Aeasy_finland_sections&request=GetMap&service=wms&styles=line&format_options=SUPEROVERLAY%3Afalse%3BKMPLACEMARK%3Afalse%3BKMSCORE%3A40%3BKMATTR%3Atrue%3B&srs=EPSG%3A4326&format=application%2Fvnd.google-earth.kmz%2Bxml&transparent=false&version=1.1.1]]></href>
            <viewRefreshMode>onStop</viewRefreshMode>
            <viewRefreshTime>1</viewRefreshTime>
         </Url>
      </NetworkLink>
      <LookAt>
         <longitude>24.4954744112449</longitude>
         <latitude>65.13640760371281</latitude>
         <altitude>0</altitude>
         <range>1467008.99690347</range>
         <tilt>0</tilt>
         <heading>0</heading>
         <altitudeMode>clampToGround</altitudeMode>
      </LookAt>
   </Folder>
</kml>

Note that you don't get the data straight away. You get a container KML. In there it's defined that after you stop scrolling, new data is fetched from the server. Google Maps will automatically supply the bounding box in the URL, and GeoServer will make sure that it gets handled properly.

It is even more easy:

SELECT askml('my_line', 'description', 
'SRID=3005;
   LINESTRING( 1190000 390000, 
     1200000 390000, 
     1200000 380000, 
     1190000 380000, 
     1190000 390000 )'::geometry);

Will easily generate kml. You can also follow the discussion about askml() here: http://www.mail-archive.com/postgis-users@postgis.refractions.net/msg01228.html