[Geoserver-users] Create View with MySQL datastore

GeoServer GeoServer User
1

Andrea, all,

Thanks for your comments. I believe I have solved the problem. I found an error in the catalina.out log file that said there was something wrong with my select statement. I’m not sure what was wrong with it but I changed it to:

create view profileszipgeom as select profiles.*, zips.the_geom from profiles left join zips on profiles.zipcodeint = zips.zipcodeint where profiles.zipcodeint != -1;

After this, I did not get an error and things seemed to be working. However, geoserver wasn’t drawing a map (the browser just kept showing the “thinking” cursor). Then I added an index to both zipcodeint fields and it seems to be working great now. Performance is very good considering the number of records I have and the table join I am doing.

Thanks,
Scott

Scott Pezanowski

email: scottpez@anonymised.com

Date: Mon, 15 Jan 2007 10:37:44 +0100
From: aaime@anonymised.com
To: scottpez@anonymised.com
CC: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Create View with MySQL datastore

Scott Pezanowski ha scritto:

Hi,

I have created a view in MySQL that simply does a join of two tables.
The sql looks something like this:

create view profileszipgeom as select profiles.*, zips.the_geom from
profiles, zips where profiles.zipcodeint != -1 and profiles.zipcodeint =
zips.zipcodeint;

Now I am trying to add this view as a featureType in Geoserver. When I
click the “generate” button in the FeatureType editor I receive the
following error:

The FeatureType ‘profileszipgeom’ has a NULL extent.
HINT: the dataset is empty or has no default geometry attribute.

I am not familiar with MySQL, but trying to make a parallel with
PostGIS, does it have a metadata table where you declare the geometry
columns, their SRS and so on?
In PostGIS in order to use view you have to create it and then register
the geometry columns in the view in the metadata table. May it be the
same with MySQL?

Cheers
Andrea

Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net’s Techsay panel and you’ll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

2

Scott Pezanowski ha scritto:

Andrea, all,
Thanks for your comments. I believe I have solved the problem. I found an error in the catalina.out log file that said there was something wrong with my select statement. I'm not sure what was wrong with it but I changed it to:
create view profileszipgeom as select profiles.*, zips.the_geom from profiles left join zips on profiles.zipcodeint = zips.zipcodeint where profiles.zipcodeint != -1;
After this, I did not get an error and things seemed to be working.

Nice. So it seems there's no need for extra metadata info in MySql.
Thank you for sharing this with us.

However, geoserver wasn't drawing a map (the browser just kept showing the "thinking" cursor). Then I added an index to both zipcodeint fields and it seems to be working great now. Performance is very good considering the number of records I have and the table join I am doing.

Ah, yeah, probably was trying to solve the query like mad before doing big sequential scans. :slight_smile:
Cheers
Andrea