Hi,
I want to load a Postgis layer, but it takes very long time to finishe loading because of mass data. Now I want to load the layer using a filter. For example, I have a field named "Road_class" in the layer, Road class can be "1", "2", "3", what shall I do if I only load the records with a "Road_class=1".
Or, do you have other solution to resolve the performance problem when loading, map manipulation, etc.
Thanks.
Senlin
Hi Senlin,
You can hook the DrawingFeatures event of the postgre layer and filter the features in the implement of that event. You can take a look at the code in the HowDoISamples\Feature Layers\StopCertainFeaturesFromDrawing. And you can just modify the code as following in your case:
PostgreSqlFeatureLayer postgreLayer = new PostgreSqlFeatureLayer(connectString, "cntry02", "oid");
postgreLayer.ZoomLevelSet.ZoomLevel01.DefaultLineStyle = LineStyles.Highway1;
postgreLayer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle.RequiredColumnNames.Add("Road_class");
postgreLayer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
postgreLayer.DrawingFeatures += new EventHandler<DrawingFeaturesEventArgs>(postgreLayer_DrawingFeatures);
private void postgreLayer_DrawingFeatures(object sender, DrawingFeaturesEventArgs e)
{
Collection<Feature> featuresToDrawn = new Collection<Feature>();
foreach (Feature feature in e.FeaturesToDraw)
{
int roadClass = Convert.ToInt32(feature.ColumnValues["Road_class"], CultureInfo.InvariantCulture);
if (roadClass == 1)
{
featuresToDrawn.Add(feature);
}
}
e.FeaturesToDraw.Clear();
foreach (Feature feature in featuresToDrawn)
{
e.FeaturesToDraw.Add(feature);
}
}
Any more questions please let me know.
Thanks,
sun
Sun,
Thank u. It works.
In fact, I want to improve the draw performance by controlling the show/hide some feature at different scale, but it seems that the performance has no difference.
I use a road layer with 150000 features[I won’t divide this layer into several layers by the Rd_class]. so I have to load all the features and display them, but I found it’s very slow when zooming or panning.
Could u please help me to improve the performance?
Senlin
Senlin,
Can you try the following code by using the ValueStyle which is exactly satisfy your
Case:
private void Form1_Load(object sender, EventArgs e)
{
winformsMap1.MapUnit = GeographyUnit.DecimalDegree;
winformsMap1.CurrentExtent = new RectangleShape(-126.4, 48.8, -67.0, 19.0);
winformsMap1.BackgroundOverlay.BackgroundBrush = new GeoSolidBrush(GeoColor.GeographicColors.ShallowOcean);
ValueStyle valueStyle = new ValueStyle();
valueStyle.ColumnName = "Road_class";
valueStyle.ValueItems.Add(new ValueItem("1", LineStyles.LocalRoad1));
string connectString = "Server=192.168.0.0;User Id=user;Password=password;DataBase=postgis;";
PostgreSqlFeatureLayer postgreLayer = new PostgreSqlFeatureLayer(connectString, "austinstreets", "oid");
postgreLayer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(valueStyle);
postgreLayer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
postgreLayer.Srid = 4326;
LayerOverlay staticOverlay = new LayerOverlay();
staticOverlay.Layers.Add("PostgreLayer", postgreLayer);
winformsMap1.Overlays.Add(staticOverlay);
postgreLayer.Open();
winformsMap1.CurrentExtent = postgreLayer.GetBoundingBox();
postgreLayer.Close();
winformsMap1.Refresh();
}
Any more quesitons just let us know.
Thanks.
Yale
Yale,
Thank U.
Yes, it work as well as Sun’s approach. But the performance got little improvement.
Senlin
Senlin,
Try following code to see performance enhancement:
Hook up the event for postgre FeatureSource as following way first:
((PostgreSqlFeatureSource)(postgreLayer.FeatureSource)).ExecutingSqlStatement += new EventHandler<ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs>(Form1_ExecutingSqlStatement);
In the event , try to change the sqlstatement which will used to get data from database:
void Form1_ExecutingSqlStatement(object sender, ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs e)
{
if (e.ExcutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBox)
{
string sqlStatement = e.SqlStatement;
e.SqlStatement = sqlStatement.TrimEnd(';') + " and Road_class='1';";
}
}
Let me know if any more questions.
Thanks.
Yale
Yale,
Thank u. It works well.
Well, now I can use the filter to load the features by a SQL script, but I got the result in overall laye, how can I remove the features outside current view?
Senlin,
Thanks for your post!
I think the resultant features returned should ONLY contains the features within the current view. Basically, you can verify this from the sqlstatement.
Any more information would be appreciated.
Thanks.
Yale
Yale,
yes, you are right. Thank U.
BTW,I copy the sqlstatement during debug:
"select AsBinary(the_geom) as the_geom,gid from streets_polyline where GeomFromWKB(:geometry,-1) && the_geom and rd_class ='5';"
I know the engin may set the value of geoetry. But how can I get or set geometry if I want to do it?
Now, How can I get current scale? I want to display the features with rd_class = '5' until I zoom in to a certain scale.
I tried to get the scale from WinformsMap but pop up an exception:
Cross-thread operation not valid: Control '' accessed from a thread other than the thread it was created on.
Senlin
Senlin,
In Postgre, the shape data is stored in binary which is different with well known binary, while the function “AsBinary” provided can be used to get well known binary from it. In reverse, the function “GeomFromWKB” provided can be used to transfer well known binary to binary stored in Postgre.
Select AsBinary("shape") from austinstreets
Select GeomFromWKB(AsBinary("shape") ) from austinstreets
About the exception happening, there are 2 ways to solve the problem:
1) Use single thread instead of multi threaded as following way:
winformsMap1.ThreadingMode = MapThreadingMode.SingleThreaded;
2) Use the following code to execute code in main thread instead of drawing thread.
private delegate void ToUIThreadDelegate(ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs e);
void Form1_ExecutingSqlStatement(object sender, ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs e)
{
this.BeginInvoke(new ToUIThreadDelegate(WorkOnThread),e);
}
private void WorkOnThread(ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs e)
{
double scale = winformsMap1.CurrentScale;
if (e.ExcutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBox)
{
string sqlStatement = e.SqlStatement;
e.SqlStatement = sqlStatement.TrimEnd(';') + " and cfcc='A31';";
}
}
Let me know if any more questions.
Thanks.
Yale
Yale,
Thank U.
And all I have done is just want to improve the performance when paning/zooming etc.
I have tried many approaches: create spatial index, loading data using a filter, dispaly feature at certain sacle. well, the performance is improved, but not as smooth as in Q-gis or uDig.
Do u have some suggestion to do it better.
below attached the code:
private void LoadPostgreLayer()
{
winformsMap1.MapUnit = GeographyUnit.Meter;
string connectString = “Server=192.168.1.101;User Id=postgres;Password=kkk;DataBase=postgis;”;
PostgreSqlFeatureLayer pgLayer_Street = new PostgreSqlFeatureLayer(connectString, “streets_polyline”, “gid”); ;
pgLayer_Street.ZoomLevelSet.ZoomLevel01.DefaultLineStyle.OuterPen = new GeoPen(GeoColor.FromArgb(200, GeoColor.StandardColors.Red), 1);
pgLayer_Street.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
((PostgreSqlFeatureSource)(pgLayer_Street.FeatureSource)).ExecutingSqlStatement += new EventHandler<ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs>(Form1_ExecutingSqlStatement);
pgLayer_Street.Open();
winformsMap1.CurrentExtent = pgLayer_Street.GetBoundingBox();
pgLayer_Street.Close();
LayerOverlay staticOverlay = new LayerOverlay();
staticOverlay.Layers.Add(“street”, pgLayer_Street);
winformsMap1.Overlays.Add(staticOverlay);
winformsMap1.Refresh();
}
void Form1_ExecutingSqlStatement(object sender, ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs e)
{
double currentScale = winformsMap1.CurrentScale;
if (e.ExcutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBox)
{
string sqlStatement = e.SqlStatement;
if (m_currentScale > 200000)
{
e.SqlStatement = sqlStatement.TrimEnd(’;’) + " and rd_class =‘1’ ;";
}
else
if (m_currentScale > 80000)
{
e.SqlStatement = sqlStatement.TrimEnd(’;’) + " and (rd_class =‘1’ or rd_class = ‘2’);";
}
else if (m_currentScale > 10000)
{
e.SqlStatement = sqlStatement.TrimEnd(’;’) + " and (rd_class =‘1’ or rd_class = ‘2’ or rd_class = ‘3’);";
}
}
}
Senlin,
It seems you are using single thread instead of multi-threaded mode? Can you try multi-threaded mode?
Any more questions let me know.
Thanks.
Yale
Yale,
Thank U for your time.
And any other method to improve more?
Senlinl
Senlin,
Sorry that I cannot think out another good solution. I will do more investigation on it later and will let you know if any findings.
Thanks.
Yale
Yale,
Thank u very much.
Senlin
Senlin,
You are welcome!
Any more questions or any findings will let you know.
Thanks.
Yale