asp.net — postgresql — google maps connection

Hi all. There is another story. In this story, i will try to connect google maps to postgresql with asp.net mvc project. Thats not all. I will add a html select element. When this element change, application will add a marker to google maps. As you think, marker data will come from postgresql database.

Cool tip

If you want to understand this story, you need to know postgresql and geometry. In my database table, all of datas are Polygon type. I need to use Point type for marker. If you dont understand this, contact me or google it.

Anyway, here we go.

Step 1 — Prepare project and view

After created asp.net mvc project, i was added cshtml view and a controller. I also included bootstrap, jquery and google maps. Here is last result ;

Step 2 — Scripts

As you can see, i added a html select element. I need to create a change event of this element. In this event, i will post selected value to my controller and wait result. Result will be contains two info; Lat and Long values. This infos links together. When this info is arrives the client side, i will add a marker to maps and delete previous one. Here is my script codes;

Step 3 — Controller codes

If you want to use postgresql in your asp.net project, you need to include npgsql package. You can find this package in Nuget Packages. This npgsql package is a database provider. After you add npgsql, you need to write your own sql codes. Try to not forget use parameters in sql command. (Sql injection)

Here is my sql code ;

select st_x(st_transform(st_centroid(geom),4326)) as lng ,st_y(st_transform(st_centroid(geom),4326)) as lat from tablo where id = 1

• st_transform = this code allows you to change default table srid. I give 4326 projection because google use this projection.
• st_centroid = As i said at the beginning, my db data are at polygon type. But i need point type. st_centroid code accepts polygon data type and gives you center point of that polygon.
• st_x = Gives you lng value of given point data.
• st_y = Gives you lat value of given point.

I will use this code in my controller. Here is my controller codes;

Step — 4 Tidy codes

While i was writing my controller codes, added comment lines for tips. Be careful use replace for javascript. As you know, comma and dot always confused. After controller codes, i will tidy my script codes. Here is result ;

Step — 5 Remove markers

For the scenario, if you add new marker to map, you will delete the previous one. You dont need to do this. But after a few markers, map does not looks clear. You know. Here is my codes;

Google maps marker object have a method called setMap. This method allows you delete or add marker to database. After this code, i can use my project with full effect.

Step — 6 Result

As you can see, my project works well. If you not understand anything or want to ask something, you can ask me. Have a good day.